asked on

Oracle Select last record in table by Id

Hi Experts

I have TableA - In that table is a string representive of datetime and an Id column. The Id column does not have to be unique.

i.e
DateTime Id
20130401 1
20130402 1
20130403 1
20130401 2
20130403 2
20130401 3

I want a Oracle select query to return a grouped by Id and the last (most recent) datetime. I.e I want to return

DateTime Id
20130403 1
20130403 2
20130401 3

How do I do this?

Thanks

Pratik Tayade

Hey,

try this:

SELECT * FROM <TBL_NAME> GROUP by Id ORDER BY DateTime DESC LIMIT 1

YZlat

use MAX(id)

YZlat

SELECT DateTime FROM tableA WHERE id = ( SELECT MAX(id) FROM tableA )

or

SELECT MAX(DateTime) FROM TableA GROUP BY Id
WHERE ID IN (SELECT MAX(ID) FROM TableA)

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrDavidThorn

ASKER

I've requested that this question be deleted for the following reason:

No correct answer

PortletPaul

At least one correct answer was given:

select
max(DateTime)
, ID
from tableA
group by
ID

suppled sample data:

CREATE TABLE TableA
	(DATETIME varchar2(12), ID int)
;

INSERT ALL 
	INTO TableA (DATETIME, ID)
		 VALUES ('20130401', 1)
	INTO TableA (DATETIME, ID)
		 VALUES ('20130402', 1)
	INTO TableA (DATETIME, ID)
		 VALUES ('20130403', 1)
	INTO TableA (DATETIME, ID)
		 VALUES ('20130401', 2)
	INTO TableA (DATETIME, ID)
		 VALUES ('20130403', 2)
	INTO TableA (DATETIME, ID)
		 VALUES ('20130401', 3)
SELECT * FROM dual
;

Open in new window

results of the query above on that data:

MAX(DATETIME)   ID
20130403        1
20130403        2
20130401        3

Open in new window

expected results:
DateTime Id
20130403 1
20130403 2
20130401 3

{edit - sorry spelling}

PortletPaul

I objected as the reason given is untrue.
For the supplied information, 2 correct (and near identical) answers were provided

by: PortletPaulPosted on 2013-04-10 at 22:46:52ID: 39065709

select
max(DateTime)
, ID
from tableA
group by
ID

by: flow01Posted on 2013-04-10 at 22:48:23ID: 39065714

select MAX(DateTime) datetime , id
FROM TableA GROUP BY Id

The only difference between these is formatting, they will perform in exactly the same way AND they will produce the "expected result".

I believe it is convention to award points to first correct answer, but have no issue with point sharing due to the minor time difference involved. I would suggest:

39065709 accepted answer
39065714 assisted answer

========
I'd really like now why the author felt the answers were incorrect though.