Finding latest record entry

Question

I have data like this.

REPORTER    SUMMARY                CREATED          UPDATED status  servicetype  
e159299 Route card from August  7/29/2013   22:46.0    5    New Route Card
e159299 I have returned the     6/11/2013   32:09.7    5    Cancel Route Card
e159324 New Route Card: RBI     1/2/2013    00:51.0    5    New Route Card
e159324 Hi, I would require     10/30/2013  35:23.0    5    New Route Card
e159299 Cancel Route Card       4/30/2013   53:26.2    5    Cancel Route Card

I need to get the latest record entry for a given reporter. i.e I need to do group by for reporter and need to get the latest record of combining created field.

My output must be like this.

 e159299    Route card from August  7/29/2013   22:46.0    5    New Route Card  
 e159324    Hi, I would require     10/30/2013  35:23.0    5    New Route Card

Wow, it really sucks that you didn't use a DATETIME column (either that or a really funny custom format is being applied). — user2864740, Nov 28 '13 at 08:13
possible duplicate of [How to select records with maximum values in two columns?](http://stackoverflow.com/questions/19981190/how-to-select-records-with-maximum-values-in-two-columns) — MT0, Nov 28 '13 at 09:01
Could you please clarify what's up with the `CREATED`/`UPDATED` columns? Specifically - what datatype are they? — Clockwork-Muse, Nov 28 '13 at 11:37

score 3 · Answer 1 · answered Nov 28 '13 at 08:20

Analyitic functions are great for this kind of queries:

select *
from (
   select reporter
   ,      summary
   ,      created
   ,      updated_status
   ,      servicetype
   ,      max(created) over (partition by reporter) last_created
   from   yout_table t)
where created = last_created;

MT0 · Answer 2 · 2013-11-28T09:38:44.757

Analytical functions are designed for this - just GROUP BY the appropriate column(s) (in this case REPORTER) and use MAX( <column_name> ) KEEP ( DENSE_RANK LAST ORDER BY <ordering_column> ) on each of the other rows.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE tbl ( REPORTER, SUMMARY, CREATED, UPDATED, status, servicetype ) AS
          SELECT 'e159299', 'Route card from August', '7/29/2013',  '22:46.0', 5, 'New Route Card' FROM DUAL
UNION ALL SELECT 'e159299', 'I have returned the',    '6/11/2013',  '32:09.7', 5, 'Cancel Route Card' FROM DUAL
UNION ALL SELECT 'e159324', 'New Route Card: RBI',    '1/2/2013',   '00:51.0', 5, 'New Route Card' FROM DUAL
UNION ALL SELECT 'e159324', 'Hi, I would require',    '10/30/2013', '35:23.0', 5, 'New Route Card' FROM DUAL
UNION ALL SELECT 'e159299', 'Cancel Route Card',      '4/30/2013',  '53:26.2', 5, 'Cancel Route Card' FROM DUAL;

Query 1:

SELECT REPORTER,
       MAX( SUMMARY     ) KEEP ( DENSE_RANK LAST ORDER BY TO_DATE( CREATED, 'MM/DD/YYYY' ) ) AS SUMMARY,
       MAX( CREATED     ) KEEP ( DENSE_RANK LAST ORDER BY TO_DATE( CREATED, 'MM/DD/YYYY' ) ) AS CREATED,
       MAX( UPDATED     ) KEEP ( DENSE_RANK LAST ORDER BY TO_DATE( CREATED, 'MM/DD/YYYY' ) ) AS UPDATED,
       MAX( status      ) KEEP ( DENSE_RANK LAST ORDER BY TO_DATE( CREATED, 'MM/DD/YYYY' ) ) AS status,
       MAX( servicetype ) KEEP ( DENSE_RANK LAST ORDER BY TO_DATE( CREATED, 'MM/DD/YYYY' ) ) AS servicetype
FROM tbl
GROUP BY REPORTER

Results:

| REPORTER |                SUMMARY |    CREATED | UPDATED | STATUS |    SERVICETYPE |
|----------|------------------------|------------|---------|--------|----------------|
|  e159299 | Route card from August |  7/29/2013 | 22:46.0 |      5 | New Route Card |
|  e159324 |    Hi, I would require | 10/30/2013 | 35:23.0 |      5 | New Route Card |

Wow, this looks more complicated than it needs to be. I prefer something more along the lines of @Alen's answer, where there's only one analytical function to deal with. What other function could you use (that might yield more unique results, too)? — Clockwork-Muse, Nov 28 '13 at 11:37
If you compare the execution plans then this is actually more efficient than @Alen's solution. There are other ways of re-writing it but I have not found any that have a lower cost execution plan than this. — MT0, Nov 28 '13 at 12:38

score -1 · Answer 3 · answered Nov 28 '13 at 08:27

-1

try this...

SELECT * FROM table ORDER BY CREATED DESC LIMIT 0,2

this query is for mysql database

answered Nov 28 '13 at 08:27

mamun0024

71
5

...The OP specifies that he has an Oracle DB, so you give him a MySQL query? Note that while this works for the sample data, this is _extremely_ unlikely to solve his actual problem; this only selects the last two rows, and he's likely to have more reporters than that. – Clockwork-Muse Nov 28 '13 at 11:33

Finding latest record entry

3 Answers3