Have Geek Brain

SQL query for the last record for each day

I have an Oracle 10g database with a table with structure and content that is 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 last record submitted for each given day. For example, with the above data, I would expect records with ID numbers 1 and 4 to be returned, since they are the latest every April 28 and April 29, respectively.

Unfortunately, I have little experience with SQL. Can anyone provide some insight on how to achieve this?

Thanks in advance!

+2


a source to share


2 answers


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



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
+5


a source


I think it is simple:

SELECT * from MyTable ORDER BY submitted DESC LIMIT 1



Though it might be worth investigating if there is some sort of column / where parameters that could make the query faster, especially if you have a query plan parser.

0


a source






More articles:


All Articles