Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Returning all columns for the maximum value

Status
Not open for further replies.

TStriker

Programmer
Nov 12, 2003
277
US
I have what I suspect is another rookie SQL question. Sometimes I want to return the contents of all columns for a certain

Example:
Code:
ITEM_NO    SEG_NO    SEQ_NO    COLOR
10015      3         1         Blue
10015      3         2         Red
10015      3         3         Green
In plain english What I'm after is the COLOR of the highest SEQ_NO where the ITEM_NO is 10015 and the SEG_NO is 3.

The natural SQL seems to be something like this:
Code:
SELECT MAX(seq_no), color FROM table where item_no = 10015 AND seg_no = 3;
Of course that doesn't work. Something about a single-row function...

This works but I suspect that there is a better way:
Code:
SELECT color FROM table WHERE item_no = 10015 AND seg_no = 3 AND seq_no = (SELECT MAX(seq_no) FROM table WHERE item_no = 10015 and seg_no = 3);
Am I coming at the the right way or is there a better way?

Thanks!

-Striker
 
An idea to try

select * from
(select *
from t1
where item_no = 10015 AND seg_no = 3
order by seq_no desc)
where rownum = 1


In order to understand recursion, you must first understand recursion.
 
Yes, your code is what I would have posted had you not posted it. <smile>

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Deuparth gwaith yw ei ddechrau.
 

Code:
select color
from my_table
where item_no = 10015 
and seq_no = 3
and seq_no = 
(select max(seq_no)
 from my_table
 where item_no=10015
 and seq_no=3);

Bill
Oracle DBA/Developer
New York State, USA
 
Clever, Taupirho!

Striker, would you please run both flavours of code (with timing on) and tell which ran faster?...and by how much?

Thanks,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Deuparth gwaith yw ei ddechrau.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top