SQL query for the latest record for each day

Question

I've got an Oracle 10g database with a table with a structure and content very similar to the following:

CREATE TABLE MyTable
(
    id         INTEGER PRIMARY KEY,
    otherData  VARCHAR2(100),
    submitted  DATE
);

INSERT INTO MyTable VALUES (1, 'a', TO_DATE('28/04/2010 05:13', ''DD/MM/YYYY HH24:MI));
INSERT INTO MyTable VALUES (2, 'b', TO_DATE('28/04/2010 03:48', ''DD/MM/YYYY HH24:MI));
INSERT INTO MyTable VALUES (3, 'c', TO_DATE('29/04/2010 05:13', ''DD/MM/YYYY HH24:MI));
INSERT INTO MyTable VALUES (4, 'd', TO_DATE('29/04/2010 17:16', ''DD/MM/YYYY HH24:MI));
INSERT INTO MyTable VALUES (5, 'e', TO_DATE('29/04/2010 08:49', ''DD/MM/YYYY HH24:MI));

What I need to do is query the database for the latest record submitted on each given day. For example, with the above data I would expect the records with ID numbers 1 and 4 to be returned, as these are the latest each for 28 April and 29 April respectively.

Unfortunately, I have little expertise as far as SQL is concerned. Could anybody possibly provide some insight as to how to achieve this?

Thanks in advance!

Answer 1

I would use a common table expression (aka CTE) like so:

With RankedItems As
    (
    Select  Id, otherdata, submitted
        , ROW_NUMBER() OVER( PARTITION BY TO_CHAR(submitted, 'YYYY-MM-DD') ORDER BY submitted DESC ) ItemRank
    From MyTable
    )
Select
From RankedItems
Where ItemRank = 1

Answer 2

I think it's as simple as:

SELECT * from MyTable ORDER BY submitted DESC LIMIT 1

Though it might be worth exploring whether there are some column/where parameters that could make the query run more quickly, particularly if you've got a query plan analyzer handy.