Tengo una tabla como la siguiente:
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 2019Quiero obtener los datos de la tabla donde el precio es máximo.
Rendimiento esperado:
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 2019Lo que probé:
Consulta 1:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (partition by "User" ORDER BY "Price" desc) as col FROM public.product)x WHERE x.col=1Producción:
User Product Price City Month Year col --------------------------------------------------------------- abc Notebook 23 Mumbai SEPTEMBER 2019 1 xyz Notebook 23 Mumbai DECEMBER 2019 1Aquí me faltan los datos del mes de diciembre de abc y lo mismo para los usuarios de xyz
Consulta 2:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (partition by "User","Price" ORDER BY "Price" desc) as col FROM public.product)x WHERE x.col=1Producción:
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 1Aquí obtengo filas para cada producto con el precio más alto
¿Puede alguien ayudarme a obtener el resultado esperado?
Simplemente puede usar una subconsulta y max() .
SELECT p1.* FROM public.product p1 WHERE p1."Price" = (SELECT max(p2."Price") FROM public.product p2);Usar dense_rank()
SELECT * FROM (SELECT *, dense_rank() OVER (ORDER BY Price desc) as col FROM public.product)x WHERE x.col=1PRODUCCIÓN:
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