CODEWITHSUNDEEP
sqlgreatest-n-per-group
Adam C
Adam C

Reputation: 9

SQL view: How do I create a view to get the top 5 highest values for five different categories?

I have a table products with prices and colours columns. I need a view to get the top five most expensive products for each colour. How would I do that?

Upvotes: 0

Views: 223

Answers (1)

Nikhil
Nikhil

Reputation: 3950

this will do :

 select * from (select a.*,rank() over (partition by colour order by price desc) rk  from 
 tableaname a) where rk<6;

rather this:

CREATE VIEW view_name AS    select * from (select a.*,rank() over (partition 
by colour order by price desc) rk  from 
tableaname a) where rk<6;

Upvotes: 1

Related Questions