CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Prabhakaran
Prabhakaran

Reputation: 4019

Group by clause with min

I am having the following table

enter image description here

I used following query and i got error message. I can identify why the error is but how can i solve it

select min(id),customer_id,created_at from thunderbolt_orders
  group by customer_id

I need the minimum id's customer_id and created_at how can i achieve it.

Upvotes: 1

Views: 191

Answers (4)

Salil
Salil

Reputation: 47532

SELECT id,customer_id,created_at 
       FROM thunderbolt_orders 
       WHERE id IN 
       (SELECT MIN(id) FROM thunderbolt_orders GROUP BY customer_id);

Upvotes: 1

Richard Newman
Richard Newman

Reputation: 630

Depending on whether or not you just want the minimum ID or whether you want the minimum ID for each customer these are the solutions.

Minimum ID:

select top 1 id,customer_id,created_at from thunderbolt_orders order by id asc

Minimum ID for each customer:

with cte as (
    select min(id) as id
    from thunderbolt_orders 
    group by customer_id
)
select *
from cte c
inner join thunderbolt_orders t on t.id = c.id

Upvotes: 0

roman
roman

Reputation: 117571

with cte as (
    select
        *, row_number() over(partition by customer_id order by id) as row_num
    from Table1
)
select *
from cte
where row_num = 1

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125534

select distinct on (customer_id)
    customer_id, id, created_at
from thunderbolt_orders
order by customer_id, id

Upvotes: 5

Related Questions