SQL - Select 3 most recent records to sysdate

[Lead_Head]

Hello all,

I need to select the three most recent records from todays date from a table.

How would I go about doing this??

Cheers,

 

[Inquisitor]

SELECT * FROM `table` ORDER BY `date` DESC LIMIT 3

 

[Visage]

He'll need a 'where trunc(DATE) = trunc(SYSDATE)' as well.....

 

[Haircut]

And SYSDATE suggests Oracle too, which means you will need to use ROWNUM to get the 3 records, not LIMIT.

 

[Lead_Head]

Yeah it's an Oracle database I'm querying from.

Rownum will only retrieve 3 records, I need to retrieve the 3 most recent dates from today per unique ID (which I have about 900).

Sorry I should have explained it better in my OP.

 

[Haircut]

Sounds like you should be able to do what you need using the RANK analytic function

http://www.adp-gmbh.ch/ora/sql/analytical/top_n.html

EDIT: Another example using partitioning
http://www.akadia.com/services/ora_analytic_functions.html#Top-N Queries

You'll probably need to partition by your unique ID and then get the top 3 ordered by sysdate descending

 

[Manlove is my forte]

Ok Redeemed myself

alter session set nls_date_format = 'dd/mm/yyyy HH:MI:SS';

SELECT        SEQUENCE#, 
              FIRST_TIME 
FROM
  (
  SELECT      SEQUENCE#,  
              FIRST_TIME 
   FROM
              krono_test 
   WHERE
              trunc(FIRST_TIME) = trunc(SYSDATE) 
   ORDER BY   FIRST_TIME DESC
   )
WHERE rownum <=3;

SEQUENCE#  FIRST_TIME
----------  -------------------
      2318   22/04/2008 07:43:46
      2317   22/04/2008 06:18:03
      2316   22/04/2008 04:56:51

 

[Lead_Head]

Thanks for your efforts chaps, however I still can't get this to work.

Using the example above, each 'sequence#' will need to return 3 'first_time' records. There are more than 3 'first_time' records per 'sequence#', so I'm returning the first three closest to today.

Does that make sense??

 

[Haircut]

You can use the analytic queries in the links I posted to do this.

e.g. if you had your tables defined as follows

create table unique_ids
(unique_id          number)
;

create table dates
(unique_id   number,
 date_field  date)
;

then you would use the following query to get the next three dates after sysdate per unique id

SELECT *
FROM 
  (SELECT unique_id, date_field, row_number()
   OVER (PARTITION BY unique_id ORDER BY date_field) next_dates
   FROM dates
   WHERE trunc(date_field) >= trunc(sysdate)
  )
WHERE next_dates <= 3

 

[Manlove is my forte]

Thanks for your efforts chaps, however I still can't get this to work.

Using the example above, each 'sequence#' will need to return 3 'first_time' records. There are more than 3 'first_time' records per 'sequence#', so I'm returning the first three closest to today.

Does that make sense??

So let me get this straight. lets say you are looking at a bunch of employees and the dates they were working.

For each employee you want to know the last 3 days they were at work... is that correct?