CODEWITHSUNDEEP
sqlgreatest-n-per-group
inail
inail

Reputation: 13

How to get only the first records from each day?

How to get only the first records from each day?

id | name | date
1  | aaa  | 2018-05-10
2  | bbb  | 2018-05-10
3  | ccc  | 2018-05-10
4  | ddd  | 2018-05-11
5  | eee  | 2018-05-11
6  | fff  | 2018-05-13

I would like to receive:

1  | aaa  | 2018-05-10
4  | ddd  | 2018-05-11
6  | fff  | 2018-05-13

Upvotes: 1

Views: 153

Answers (3)

user330315
user330315

Reputation:

For Postgres there is a more efficient solution:

select distinct on (id) id, name, date
from the_table
order by id, date;

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

If ID is Pk,this query can work on PostgreSQL and MySql

Writing subquery to get MIN id by date in the table,then self join

SELECT t1.*
FROM (
  SELECT MIN(ID) minId
  FROM T1 
  GROUP BY date
) t inner join t1 on t.minid = t1.id

sqlfiddle:http://sqlfiddle.com/#!9/9a48d7/4

Upvotes: 0

shrek
shrek

Reputation: 887

CREATE TABLE T1
    (id int, name varchar2(3), dat timestamp)
;

INSERT ALL 
    INTO T1 (id, name, dat)
         VALUES (1, 'aaa', '10-May-2018 12:00:00 AM')
    INTO T1 (id, name, dat)
         VALUES (2, 'bbb', '10-May-2018 12:00:00 AM')
    INTO T1 (id, name, dat)
         VALUES (3, 'ccc', '10-May-2018 12:00:00 AM')
    INTO T1 (id, name, dat)
         VALUES (4, 'ddd', '11-May-2018 12:00:00 AM')
    INTO T1 (id, name, dat)
         VALUES (5, 'eee', '11-May-2018 12:00:00 AM')
    INTO T1 (id, name, dat)
         VALUES (6, 'fff', '13-May-2018 12:00:00 AM')
SELECT * FROM dual
;

Query -

select id, name, dat from (
select id, name, dat, dense_rank() over (partition by dat order by id) as rnk
from t1
  ) where rnk = 1;



ID  NAME    DAT
1   aaa 2018-05-10 00:00:00.0
4   ddd 2018-05-11 00:00:00.0
6   fff 2018-05-13 00:00:00.0

Upvotes: 0

Related Questions