Group by clause with min

Question

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.

possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Erwin Brandstetter, Aug 23 '13 at 12:50
Is it just the lowest possible ID and it's customer_id and created_at or is it the lowest ID for each customer_id and then the information back — Richard Newman, Aug 23 '13 at 13:04

score 5 · Answer 1 · answered Aug 23 '13 at 12:32

5

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

answered Aug 23 '13 at 12:32

Clodoaldo Neto

118,695
26
233
260

score 2 · Answer 2 · answered Aug 23 '13 at 12:33

2

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

answered Aug 23 '13 at 12:33

Roman Pekar

107,110
28
195
197

score 1 · Answer 3 · edited Aug 07 '14 at 11:14

1

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

edited Aug 07 '14 at 11:14

Naveen Kumar Alone

7,536
5
36
57

answered Aug 23 '13 at 12:28

Salil

46,566
21
122
156

@NaveenKumar:- Have you check my edited answer? I think you are not, which i edited before your answer and comment. – Salil Aug 23 '13 at 12:31

Richard Newman · Answer 4 · 2013-08-23T13:08:15.937

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

Group by clause with min

4 Answers4