I've searched a lot, asked my DBA/Java developer friends, but did not find a satisfactory answer.
Here is my table (Showing only 3 columns)
TBL_ Jobs
Job_ID(auto increment PK|Job_posting_date| city|Already_applied_flag
------------------------------------------------------------------------
1 | 1/1/2001 | Tezpur |
2 | 1/1/2010 | Jakarta| X(already applied)
3 | 1/1/2003 | Paris|
----Thousands of rows...
Every day, my java program picks 50 jobs from the Jobs table, applies to them and then , marks them as already applied so that it will not apply to the same jobs again.
The problem is, the table is not ordered according to the job_posting_date, but my program has to pick the LATEST jobs first. (of course, as we keep running it, it will start applying to OLD jobs )
So my question is: How do I select 50 LATEST jobs every day? The solutions I am thinking of
1) SELECT a row for MAX( Job_posting_date )
apply to the job. Mark it as applied
Repeat 50 times
----Extremely inefficient
2) Select ALL the Rows:
Order by (Job_posting_date)
Pick the top 50
3) Somehow reorder the database table ? ( JOB_ID is a foreign key in other tables, so changing it won't be easy)
This seems like a common problem to have. What am I missing here ? Thanks
In general rows in relational database tables do not have order (database specifics to aside). Ordering result is done in JPQL and limiting number of results via Query.setMaxResults. For example:
String jpql = "SELECT j FROM Jobs ORDER BY jobPostingDate";
Query q = em.createQuery(jpql);
q.setMaxResults(50);