CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Elad8
Elad8

Reputation: 11

adding a column to a query without inserting it to the GROUP BY clause

I'm trying to select the followings from my product sale DB: the recent purchase of each product, the sale price and product_id. all in one query.

first, I created a query to collect just the product_id and recent sale of each product (without price) grouped by product_id and it worked fine. here is the query:

SELECT product_id,
        max(sold_date)
FROM sold_list
GROUP BY product_id
ORDER BY product_id

BUT when I try to add the sold_price column, postgres forces me to put the sold_price in the GROUP BY clause. and when I do that I start getting more than one purchase of a certain product instead of having only the recent purchase of each product.

this is what I tried:

SELECT product_id,
       max(sold_date),
       sold_price
FROM sold_list
GROUP BY product_id, sold_price
ORDER BY product_id

What is the way to have those 3 columns together and having only one recent purchase of each product_id (each product_id to show only one time in the column)?

Upvotes: 0

Views: 667

Answers (3)

Belayer
Belayer

Reputation: 14886

Your description leads me to think you want each sold_price and the last (max) date the product sold. If that is so then you can create a CTE to get the date then to the main select gathering the product and prices. See fiddle.

with prod_date(product_id, sold_date) as
     (select product_id, max(sold_date) 
        from sold_list
       group by product_id
     )
select sl.product_id
     , pd.sold_date max_sold_date 
     , sl.sold_price
  from sold_list sl
  join prod_date pd on pd.product_id=sl.product_id  
 order by product_id;

Upvotes: 0

Mike Organek
Mike Organek

Reputation: 12484

With PostgreSQL, you can use distinct on:

SELECT distinct on (product_id) product_id, sold_date, sold_price
  FROM sold_list
 ORDER BY product_id, sold_date desc

Upvotes: 1

Raghav Garg
Raghav Garg

Reputation: 3707

You need to aggregate the column if it's not in group by.

One possible way could be to use array_agg (Postgres Aggregate Functions Docs)

SELECT product_id,
       max(sold_date),
       array_agg(sold_price), -- all the sold_price
       array_agg(DISTINCT sold_price) -- unique sold_price
FROM sold_list
GROUP BY product_id
ORDER BY product_id

This will return a column with array.

Upvotes: 0

Related Questions