How can i select only id of min created date in each group

Question

Imagine next tables

Ticket Table

 ========================
| id | question          |
 ========================
| 1  | Can u help me :)? |
 ========================

UserEntry Table

 ======================================================
| id | answer               | dateCreated  | ticket_id |
 ======================================================
| 2  | It's my plessure :)? | 2016-08-05   |     1     |     
=======================================================
| 3  | How can i help u ?   | 2016-08-06   |     1     |
 ======================================================

So how can I only get id of row for each group which have min date value

So my expected answer should be like that

 ====
| id |
 ====
| 2  |
 ====

UPDATE:

I got the solution in next query

SELECT id FROM UserEntry WHERE datecreated IN (SELECT MIN(datecreated) FROM CCUserEntry GROUP BY ticket_id)

Improved Answer

SELECT id FROM UserEntry WHERE (ticket_id, datecreated) IN (SELECT ticket_id, MIN(datecreated) FROM UserEntry GROUP BY ticket_id);

Also this is a good and right answer too (NOTE: DISTINCT ON is not a part of the SQL standard.)

SELECT DISTINCT ON (ue.ticket_id) ue.id FROM UserEntry ue ORDER BY ue.ticket_id, ue.datecreated

Help us help you - please share the tables' structures, some sample data, and the result you're trying to get for it. — Mureinik, Feb 13 '17 at 21:39
@Andomar i saw your edits but i was already edited what u did, so why i didn't accept your edits, anyway thanks for ur help :) — mibrahim.iti, Feb 13 '17 at 21:54
@Mureinik i edited my question and added a sample design of tables. — mibrahim.iti, Feb 13 '17 at 21:58
I don't understand. You `SELECT entry.id , MIN(entry.datecreated)`, but you only want `entry.id`? Then `SELECT entry.id` only. Where is the problem? — Thorsten Kettner, Feb 13 '17 at 22:02
The result you are showing doesn't match the query. With the query shown you don't select only ID 2, you select ID 3, too. — Thorsten Kettner, Feb 13 '17 at 22:07
@ThorstenKettner i am forced to use *MIN(entry.datecreated)* because i need the row in each group with min createdDate and i don't know how i select only id and move this *MIN(entry.datecreated)* out side selection, got me ? — mibrahim.iti, Feb 13 '17 at 22:51
@ThorstenKettner imagine i have a date which i am grouping it by ticket id, like in example, so ignore the query in question, i will delete it so no one will be confused, so how i select first id of min created date in each group — mibrahim.iti, Feb 13 '17 at 22:53

Thorsten Kettner · Accepted Answer · 2017-02-13T23:16:14.317

7

It seems you want to select the ID with the minimum datecreated. That is simple: select the minimum date and then select the id(s) matching this date.

SELECT id FROM UserEntry WHERE datecreated = (SELECT MIN(datecreated) FROM UserEntry);

If you are sure you won't have ties or if you are fine with just one row anyway, you can also use FETCH FIRST ROW ONLY which doesn't have a tie clause in PostgreSQL unfortunately.

SELECT id FROM UserEntry ORDER BY datecreated FETCH FIRST ROW ONLY;

UPDATE: You want the entry ID for the minimum date per ticket. Per ticket translates to GROUP BY ticket_id in SQL.

SELECT ticket_id, id FROM UserEntry WHERE (ticket_id, datecreated) IN
  (SELECT ticket_id, MIN(datecreated) FROM UserEntry GROUP BY ticket_id);

The same can be achieved with window functions where you read the table only once:

SELECT ticket_id, id
FROM
(
  SELECT ticket_id, id, RANK() OVER (PARTITION BY ticket_id ORDER BY datecreated) AS rnk
  FROM UserEntry 
) ranked
WHERE rnk = 1;

(Change SELECT ticket_id, id to SELECT id if you want the queries not to show the ticket ID, which would make the results harder to understand of course :-)

edited Feb 13 '17 at 23:16

answered Feb 13 '17 at 22:05

Thorsten Kettner

89,309
7
49
73

i want to select minimum id in each group, so for example if i have group of 2 i want to select only id of minimum date, got me ? – mibrahim.iti Feb 13 '17 at 22:49
You mean the entry ID for the minimum date per ticket? – Thorsten Kettner Feb 13 '17 at 22:51
yes exactly :) each row id which have min createdDate per tickets – mibrahim.iti Feb 13 '17 at 22:56
Okay, I've updated my answer accordingly. – Thorsten Kettner Feb 13 '17 at 23:16
Thank u very much, actually your first answer helped me much, and of course your answer after edit is acceptable too, anyway i updated my post before i see your updates, but i will do accept your answer because you helped much and the answer is right too – mibrahim.iti Feb 13 '17 at 23:27
Your own solution is incorrect. You select the minimum ID per ticket alright, but then you select any row that happens to match one of the found dates regardless of the ticket. – Thorsten Kettner Feb 13 '17 at 23:43
i updated my answer with your answer, but i think your answer will have same problem too, i mean my answer above like yours, i see only difference is using * WHERE (ticket_id, datecreated)* >> ticket_id with where condition – mibrahim.iti Feb 14 '17 at 00:02
That is the difference. When I find that 2016-08-05 is the minimum date for ticket 1, I select the ID for the ticket 1 record with that date. When you find that 2016-08-05 is the minimum date for ticket 1, you select all IDs for all ticket records that happen to have that date. – Thorsten Kettner Feb 14 '17 at 00:05
yes you are right, got your point now, i was wrong, Thanks for your help, and it's really good point to understand – mibrahim.iti Feb 14 '17 at 00:26
This is all needless complication. Simply use `DISTINCT ON` (see [answer to dupe question](http://stackoverflow.com/a/7630564/939860)) - or other query techniques to [optimize performance in certain situations](http://stackoverflow.com/a/25536748/939860). – Erwin Brandstetter Feb 14 '17 at 03:16

score 6 · Answer 2 · answered Feb 13 '17 at 22:28

6

You may want fetch first row only or distinct on (if you care about more than one ticket):

SELECT DISTINCT ON (ue.ticket_id) ue.id 
FROM UserEntry ue
ORDER BY ue.ticket_id, ue.date_created

This will get the id on the row with the minimum date_created value.

answered Feb 13 '17 at 22:28

Gordon Linoff

1,242,037
58
646
786

the query must use **GROUP BY** because i want id of row which have *MIN(datecreated)* for each group (I want to group by ticket) per ticket – mibrahim.iti Feb 13 '17 at 23:13
@mibrahim.iti: The query looks correct to me. Have you even tried it? Do you know what PostgreSQL's `DISTINCT ON` does? – Thorsten Kettner Feb 14 '17 at 00:00
@ThorstenKettner yes but didn't work with me, i see also it's correct but it doesn't work, anyway it's just DISTINCT for postgres – mibrahim.iti Feb 14 '17 at 00:13
@ThorstenKettner yes the query is incorrect, it's fire *ORDER BY ue.ticket_id, ue.datecreated* actually it needs *ue.datecreated* to be in selection too, i added it to query and tested but it return wrong answer too, it return all entries not just min – mibrahim.iti Feb 14 '17 at 00:20
The final query tested by me is **SELECT DISTINCT (ue.ticket_id), ue.id , ue.datecreated FROM UserEntry ue where ticket_id = 86523 ORDER BY ue.ticket_id, ue.datecreated** and that ticket id have two rows so i was expecting only one anyway even it have more than two rows, but i got two rows which means the query is wrong – mibrahim.iti Feb 14 '17 at 00:21
4

@mibrahim.iti . . . No, the query uses `DISTINCT ON`. That is very, very different from `SELECT DISTINCT`. If I had intended the latter, I would have written it. – Gordon Linoff Feb 14 '17 at 02:29
@GordonLinoff Yes, you are right, Your query is different and there are difference between DISTINCT and DISTINCT ON, Thanks for your helpful answer :) – mibrahim.iti Feb 14 '17 at 08:06
@mibrahim.iti . . . This is usually the simplest and best performing way to do what you are trying to do. – Gordon Linoff Feb 14 '17 at 12:23
1

To add clarity to this answer: everything works like a charm: 1. It sorts the table on _id (treat is as group by _id) 2. Inside the "groups" sorts it by creation date (treat is as min(created_at) 3. Then distinct on takes the first entry for id => it always takes the row with min(created_at) – cepbuch Oct 28 '20 at 13:44

score 0 · Answer 3 · answered Feb 13 '17 at 22:33

A solution with ANSI SQL that works on a wide range of DBMS that support modern SQL is to use window functions:

select id
from (
   select id, row_number() over (partition by ticket_id order by date_created) as rn
   from userentry
) t
where rn = 1;

Note that in Postgres, Gordon's solution using distinct on () is usually faster then using window functions

How can i select only id of min created date in each group

3 Answers3