sqlite filter values based on destinct values in another column

Question

I have a TABLE like this

unixdatetime INTEGER PRIMARY KEY,  UserId TEXT,  Latitude TEXT    
1505144748                         ABC123        51.6N
1505309523                         ABC123        51.6N
1505320731                         BCD234        51.6N
1505378502                         BCD234        51.6N
1507382309                         BCD234        51.6N

I need to get a one Latitude result for each DISTINCT UserID, so my result would be [(51.691N), (51.691N)]

Im trying

c.execute('select DISTINCT Latitude from TABLE where UserID in (select DISTINCT UserID from TABLE)').fetchall()

But that only gives me 1 result, I understand thats because Im asking for DISTINCT Latitude, I just dont know how to ask SQLite for what Im looking for. max(Latitude) does the same.

Which 1 result do you want for a user? The most recent?

Alex K.
– Alex K.

2017-09-19 13:59:54 +00:00
Commented Sep 19, 2017 at 13:59 — Alex K.
– Alex K., Commented Sep 19, 2017 at 13:59
That would be fine

Scalextrix
– Scalextrix

2017-09-19 14:00:29 +00:00
Commented Sep 19, 2017 at 14:00 — Scalextrix
– Scalextrix, Commented Sep 19, 2017 at 14:00

Chris · Accepted Answer · 2017-09-19 14:00:58Z

1

You could use GROUP BY:

c.execute('select Latitude from TABLE group by UserId')

answered Sep 19, 2017 at 14:00

Chris

23.2k4 gold badges58 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scalextrix Over a year ago

OK thanks, thats works, I had not properly understood what group by did and had failed to use it correctly in an earlier try

Collectives™ on Stack Overflow

sqlite filter values based on destinct values in another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related