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 .....
cbridgmanAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
SQL Server DOES have ROW_NUMBER() (from version 2005 onward).

The difference is that Oracle allows subqueries without an alias, but SQL Server does not. Due to the expectation that an alias exists it finds the keyword WHERE instead, and hence you get that error message.

Try this, it will work in both:
SELECT
    a.rn
  , a.assetnum
  , a.description
  , a.purchaseprice
FROM (
    SELECT
        assetnum
      , description
      , purchaseprice
      , ROW_NUMBER() OVER (ORDER BY purchaseprice DESC) rn
    FROM asset
    ) a             --<<< this alias was missing
WHERE a.rn <= 4
ORDER BY a.rn
;

Open in new window

0
 
cbridgmanAuthor Commented:
I made an error in my query. It should have read:

select assetnumber, assetdescription, assetprice from assettable
where assetprice = (select max(assetprice) from asset)
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Something like:
  select assetnumber, assetdescription, assetprice from assettable
  where assetprice = (select assetprice from asset order by assetprice desc where rownum=2);
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Aneesh RetnakaranDatabase AdministratorCommented:
Why don't you order by that column

select  assetnumber, assetdescription, assetprice from assettable
order by assetprice desc
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You have selected both MSSQL and Oracle - do you need a generic or specific solution?
0
 
slightwv (䄆 Netminder) Commented:
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
/
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
:( 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);
0
 
cbridgmanAuthor Commented:
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.
0
 
sdstuberCommented:
slightwv's query should work with both
0
 
slightwv (䄆 Netminder) Commented:
>>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.

If you want 3 queries just take my query and alter the where clause:
...
where rn=1

...
where rn=2

...
where rn=3

etc...
0
 
cbridgmanAuthor Commented:
Hi Slightwv, your query is just what I was looking for. It works like a charm. It does not work with SQL Server though because SQL Server doesn't have the row number thing. When I run it there, I get the following error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.

select rn, assetnum, description, purchaseprice from (
select assetnum, description, purchaseprice,
row_number() over(order by purchaseprice desc) rn
 from asset
)
where rn<=4
order by rn

Any ideas on how to do this for SQL Server?
0
 
cbridgmanAuthor Commented:
Thanks for your help. Everything works like a charm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.