CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-groupsysdate
Soumya
Soumya

Reputation: 893

Get the latest entry for a given day in Oracle

I want to select

 data12 [last entry for 12-21-2014],
 data11 [last entry for 12-20-2014],
 data8  [last entry for 12-19-2014]

from the below table.

   snapshot_datestamp   data
-------------------------------   
 12-21-2014 08:24:21    data12
 12-20-2014 19:58:49    data11
 12-20-2014 19:55:36    data10
 12-20-2014 19:53:59    data9
 12-19-2014 21:56:23    data8
 12-19-2014 21:13:16    data7
 12-19-2014 11:05:45    data6
 12-19-2014 11:05:07    data5
 12-19-2014 10:56:13    data4
 12-19-2014 10:52:21    data3
 12-19-2014 10:50:43    data2
 12-19-2014 10:49:30    data1

Not quite sure how to achieve this.Any pointer will be a great help.

Upvotes: 0

Views: 273

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93724

Simple way is by using Order by and ROWNUM

SELECT *
FROM   (SELECT data
        FROM   tablename
        WHERE  trunc(snapshot_datestamp)  = TO_DATE('12-21-2014','MM-DD-YYYY')
        ORDER  BY snapshot_datestamp DESC)
WHERE  ROWNUM = 1;

Upvotes: 1

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

Assuming there isn't any key in the data we can use here, using ROW_NUMBER is probably one solution:

SELECT "snapshot_datestamp", "data" FROM
(

    SELECT "snapshot_datestamp", "data",
           ROW_NUMBER() 
            OVER (PARTITION BY TRUNC("snapshot_datestamp")
                  ORDER BY "snapshot_datestamp" DESC) rn
    FROM T
) V
WHERE rn = 1
ORDER BY 1 DESC

The idea here is to number each row for a given date (in descending order based on their "timestamp"). Once done, the "last" entry for each partition is simply the row numbered as 1 in that partition.

See http://sqlfiddle.com/#!4/df1708/3

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

One way is to get the latest time per day, then select the corresponding records:

select
  trunc(snapshot_datestamp),
  data
from mytable
where snapshot_datestamp in
(
  select max(snapshot_datestamp)
  from mytable
  group by trunc(snapshot_datestamp)
)
order by trunc(snapshot_datestamp);

Another is to use an analytic function:

select 
  trunc(snapshot_datestamp),
  max(data) keep (dense_rank last order by snapshot_datestamp)
from mytable
group by trunc(snapshot_datestamp)
order by trunc(snapshot_datestamp);

Upvotes: 1

Related Questions