Home » SQL & PL/SQL » SQL & PL/SQL » Select - how to get the value of the last row for each key
icon5.gif  Select - how to get the value of the last row for each key [message #292655] Wed, 09 January 2008 09:22 Go to next message
amalcaide
Messages: 1
Registered: January 2008
Junior Member
[FONT=Courier]
Giving this table:

Key Seq Value
A 1 30
A 2 26
A 3 80
B 1 70
B 2 10
C 1 04
D 1 90
D 2 45

How can I make a select to have the following result:

Key Seq Value
A 3 80
B 2 10
C 1 04
D 2 45

That is, how to get the value of the last row for each key?

Any help would be appreciated.
Re: Select - how to get the value of the last row for each key [message #292658 is a reply to message #292655] Wed, 09 January 2008 09:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
search for analytic functions in oracle sql reference manual. Clue is row_number(), rank(), dense_rank, max().

Regards

Raj
Re: Select - how to get the value of the last row for each key [message #292664 is a reply to message #292658] Wed, 09 January 2008 09:47 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Assuming the key seq combination is unique, something like this could also work.
select key,seq,val from seq_table s where seq in 
(select max(seq)  from seq_table s1 where s1.key = s.key) 

[Updated on: Wed, 09 January 2008 09:48]

Report message to a moderator

Re: Select - how to get the value of the last row for each key [message #292665 is a reply to message #292655] Wed, 09 January 2008 09:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Or you can take the max(seq) grouping by key in the inner query and select * from table where (key,seq) in (inner query).

By
Vamsi
Re: Select - how to get the value of the last row for each key [message #292786 is a reply to message #292665] Wed, 09 January 2008 20:27 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or go aggregate rather than analytic
SELECT key, max(seq), max(val) KEEP (DENSE_RANK LAST ORDER BY seq)


Ross Leishman
Previous Topic: Bulk collect
Next Topic: SQL command not properly ended
Goto Forum:
  


Current Time: Sun Dec 04 14:21:24 CST 2016

Total time taken to generate the page: 0.09839 seconds