Select all rows based on alternate publisher
I want to list all rows by alternate publisher with a price increase, see table below.
id publisher price
1 ABC 100.00
2 ABC 150.00
3 ABC 105.00
4 XYZ 135.00
5 XYZ 110.00
6 PQR 105.00
7 PQR 125.00
Expected Result:
id publisher price
1 ABC 100.00
6 PQR 105.00
5 XYZ 110.00
3 ABC 105.00
7 PQR 125.00
4 XYZ 135.00
2 ABC 150.00
What should be the SQL?
source to share
This should do it:
select id, publisher, price
from (
select id, publisher, price,
row_number() over (partition by publisher order by price) as rn
from publisher
) t
order by rn, publisher, price
Window functions assign unique numbers to each publisher price. Based on this, the default outer ordering will first display all rows with rn = 1, which are the rows for each lowest priced publisher. The second row for each publisher has the second lowest price, and so on.
SQLFiddle example: http://sqlfiddle.com/#!4/06ece/2
source to share
SELECT id, publisher, price
FROM tbl
ORDER BY row_number() OVER (PARTITION BY publisher ORDER BY price), publisher;
You cannot use the output of window functions in sentences WHERE
or HAVING BY
because window functions are applied after them. But you can use window functions in a sentence ORDER BY
.
source to share
if I succeed. You must use the ROW_NUMBER () function to change prices within each publisher, and then order that range and publisher.
SELECT ID,
Publisher,
Price,
Row_number() OVER (PARTITION BY Publisher ORDER BY Price) as rn
FROM T
ORDER BY RN,Publisher
source to share