SQL To Find 1st highest, 2nd highest, 3rd highest, etc. records
I have a table with the following columns: assetnumber, assetdescription, and assetprice. Assetnumber is unique. To find the row with the highest assetprice and include all 3 columns in the result set I would run a query like the following:
select assetnumber, assetdescription, and assetprice from assettable
where assetprice = (select max(assetprice) from asset)
How would I find the row with the 2nd highest price? How about the 3rd, then the 4th .....
select assetnumber, assetdescription, and assetprice from assettable
where assetprice = (select max(assetprice) from asset)
How would I find the row with the 2nd highest price? How about the 3rd, then the 4th .....
Something like:
select assetnumber, assetdescription, assetprice from assettable
where assetprice = (select assetprice from asset order by assetprice desc where rownum=2);
select assetnumber, assetdescription, assetprice from assettable
where assetprice = (select assetprice from asset order by assetprice desc where rownum=2);
Why don't you order by that column
select assetnumber, assetdescription, assetprice from assettable
order by assetprice desc
select assetnumber, assetdescription, assetprice from assettable
order by assetprice desc
You have selected both MSSQL and Oracle - do you need a generic or specific solution?
For Oracle try:
select rn, assetnumber, assetdescription, assetprice from (
select assetnumber, assetdescription, assetprice,
row_number() over(order by assetprice desc) rn
from assettable
)
where rn<=4
order by rn
/
select rn, assetnumber, assetdescription, assetprice from (
select assetnumber, assetdescription, assetprice,
row_number() over(order by assetprice desc) rn
from assettable
)
where rn<=4
order by rn
/
:( the 'where rownum=2' is not working, there is a somewhat more cryptic way to get the 2nd from the list:
select assetnumber, assetdescription, assetprice from assettable
where assetprice =( select assetprice from ( select assetprice from ( select assetprice from asset order by assetprice desc) whererownum < 3 order by 1 ) where rownum = 1);
select assetnumber, assetdescription, assetprice from assettable
where assetprice =( select assetprice from ( select assetprice from ( select assetprice from asset order by assetprice desc) whererownum < 3 order by 1 ) where rownum = 1);
ASKER
I think what I would like is an Oracle version of the script and an SQL Server version. If they are the same, that's even better. With regard to the result set, I want the result set to contain exactly one row. The first query would ask for the row with the highest asset price. The second query would ask for the row with the second highest asset price. The 3rd query would ask for the row with the 3rd highest asset price. Etc.
I hope that helps.
I hope that helps.
slightwv's query should work with both
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help. Everything works like a charm
ASKER
select assetnumber, assetdescription, assetprice from assettable
where assetprice = (select max(assetprice) from asset)