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

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

Answer 1

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 :-)