I want to select the first row of each group grouped by
the PostgreSQL keyword GROUP BY. Suppose I am working on purchase reporting and
I want to select the customer who has a max value of the price.
I have the following table
Result:
I have the following table
CREATE TABLE orders
(
id serial not null,
name text,
itemQty integer,
price decimal
);
--Insert some records
insert into orders(name,itemqty, price)
values('Ashish',2,200);
insert into orders(name,itemqty, price)
values('Naina',8,300);
insert into orders(name,itemqty, price)
values('Mamta',10,2000);
insert into orders(name,itemqty, price)
values('Ashish',4,500);
insert into orders(name,itemqty, price)
values('Naina',4,100);
insert into orders(name,itemqty, price)
values('Mamta',6,2500);
insert into orders(name,itemqty, price)
values('Mamta',5,1500);
|
Simple
select query
SELECT * FROM orders
Result:
But
I want to result as below:
There are several ways to resolve this problem, please
the below PostgreSQL statements/t-sql queries.
1- Using common table expression:
WITH cte AS (
SELECT
p.id,
p.name,
p.itemqty,
p.price,
ROW_NUMBER() OVER(PARTITION BY p.name
ORDER BY
p.price DESC) AS rnk
FROM orders p)
SELECT o.*
FROM
cte o
WHERE
o.rnk = 1;
|
Result:
2- Using DISTINCT ON keyword
SELECT DISTINCT ON (name)
id, name, itemqty, price
FROM orders
ORDER BY name, price DESC, id;
|
Result:
3- Using row_number() in subquery
SELECT id, name, itemqty, price
FROM (
SELECT
id,name, itemqty, price
, row_number() OVER(PARTITION BY name ORDER BY price DESC) AS rn
FROM orders
)
tmp
WHERE rn = 1;
|
4- Using array_agg() with ORDER BY
SELECT (array_agg(id ORDER BY price DESC))[1] AS id
,
name
,
max(price) AS price
FROM orders
GROUP BY name;
|
