0

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

GOPI
  • 1,042
  • 8
  • 30
Sourabh Bora
  • 3
  • 1

1 Answers1

0

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);
Mikko Maunu
  • 41,366
  • 10
  • 132
  • 135
  • Thanks! I also found this post, where it says: orderby happens first, and then, limit is imposed. Hence it will serve my purpose. http://stackoverflow.com/questions/4708708/mysql-order-by-limit – Sourabh Bora Jan 05 '14 at 16:52