SQLite - SELECT DISTINCT of one column and get the others

Question

This has been touched on before on this website. I want distinct on group but I also want to get the other fields too. what I need is the lowest id of each group, but instead I get the highest. I've tried variod SQL queries and the nearest 2 that work are

1)

select * 
from reminder 
group by Eventgroup 
order by autoid

2)

SELECT distinct Autoid,EventDate,Subject,birthdate,Eventgroup 
from reminder 
group by Eventgroup 
order by autoid

Data:

EventDate    Subject          birthdate       Eventgroup    autoid

09/10/2017   Joes Birthday     09/10/1995      4            9
13/07/2017   Bill Birthday     13/07/1999      2            8
04/04/2017   Tony Birthday     04/04/1993      3            7
09/10/2016   Joes Birthday     09/10/1995      4            6
13/07/2016   Bill Birthday     13/07/1999      2            5
04/04/2016   Tony Birthday     04/04/1993      3            4
09/10/2015   Joes Birthday     09/10/1995      4            3
13/07/2015   Bill Birthday     13/07/1999      2            2
04/04/2015   Tony Birthday     04/04/1993      3            1

both of these queries return

09/10/2017   Joes Birthday     09/10/1995      4            9
13/07/2017   Bill Birthday     13/07/1999      2            8
04/04/2017   Tony Birthday     04/04/1993      3            7

what I want is the earliets dates such as

09/10/2015   Joes Birthday     09/10/1995      4            3
13/07/2015   Bill Birthday     13/07/1999      2            2
04/04/2015   Tony Birthday     04/04/1993      3            1

possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Barmar, Mar 05 '15 at 18:20
I did have a look at this but I couldn't get it to work with SQLLite — Mick Burden, Mar 05 '15 at 19:25
Not sure why you couldn't get it to work. See my answer, it works for me. — Barmar, Mar 05 '15 at 19:41

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

Join the table with a subquery that finds the earliest date for each event group.

SELECT a.*
FROM reminders a
JOIN (SELECT eventgroup, MIN(eventdate) mindate
      FROM reminders
      GROUP BY eventgroup) b
ON a.eventgroup = b.eventgroup AND a.eventdate = b.mindate

This is the same structure as the second query in this answer in the duplicate question.

DEMO

edited May 23 '17 at 12:06

Community

1
1

answered Mar 05 '15 at 19:38

Barmar

741,623
53
500
612

SQLite - SELECT DISTINCT of one column and get the others

1 Answers1