I have a table like below:
User Product Price City Month Year
------------------------------------------------
abc Pen 22 Mumbai JULY 2019
abc Pencil 21 Mumbai AUGUST 2019
abc Notebook 23 Mumbai SEPTEMBER 2019
abc Pen 22 Mumbai OCTOBER 2019
abc Pencil 21 Mumbai NOVEMBER 2019
abc Notebook 23 Mumbai DECEMBER 2019
xyz Pen 22 Mumbai JULY 2019
xyz Pencil 21 Mumbai AUGUST 2019
xyz Notebook 23 Mumbai SEPTEMBER 2019
xyz Pen 22 Mumbai OCTOBER 2019
xyz Pencil 21 Mumbai NOVEMBER 2019
xyz Notebook 23 Mumbai DECEMBER 2019
I want to get the data from table where the price is maximum.
Expected Output:
User Product Price City Month Year
----------------------------------------------------
abc Notebook 23 Mumbai SEPTEMBER 2019
abc Notebook 23 Mumbai DECEMBER 2019
xyz Notebook 23 Mumbai SEPTEMBER 2019
xyz Notebook 23 Mumbai DECEMBER 2019
What I tried:
Query 1:
SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (partition by "User" ORDER BY "Price" desc) as col
FROM public.product)x
WHERE x.col=1
Output:
User Product Price City Month Year col
---------------------------------------------------------------
abc Notebook 23 Mumbai SEPTEMBER 2019 1
xyz Notebook 23 Mumbai DECEMBER 2019 1
Here I am missing December month data of abc and same for xyz users
Query 2:
SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (partition by "User","Price" ORDER BY "Price" desc) as col
FROM public.product)x
WHERE x.col=1
Output:
User Product Price City Month Year col
--------------------------------------------------------------
abc Notebook 23 Mumbai SEPTEMBER 2019 1
abc Pen 22 Mumbai JULY 2019 1
abc Pencil 21 Mumbai AUGUST 2019 1
xyz Notebook 23 Mumbai DECEMBER 2019 1
xyz Pen 22 Mumbai JULY 2019 1
xyz Pencil 21 Mumbai NOVEMBER 2019 1
Here I am getting rows for every products highest price
Can someone help me to get Expected output?
You can simply use a subquery and max().
SELECT p1.*
FROM public.product p1
WHERE p1."Price" = (SELECT max(p2."Price")
FROM public.product p2);
Use dense_rank()
SELECT * FROM
(SELECT *, dense_rank() OVER (ORDER BY Price desc) as col
FROM public.product)x
WHERE x.col=1
OUTPUT:
user product price city month year col
Xyz Notebook 23 Mumbai SEPTEMBER 2019 1
abc Notebook 23 Mumbai DECEMBER 2019 1
abc Notebook 23 Mumbai SEPTEMBER 2019 1
xyz Notebook 23 Mumbai DECEMBER 2019 1