Sort by time in SQL

Question

select  
logs.uid,
logs.event_value

from publicview.treasurehunters_ok logs
left join public.app_ok_cbaiodclebabababa_agg_users users  on logs.uid=users.uid
where  first_event_time>='01.09.2017 0:00:00'
and first_event_time <= '11.09.2017 0:00:00'
and logs.event ='balance_OK'

I need to sort each uid by time in ascending order and leave only the first values for logs.event_value

I.e

uid  logs.event_value
1      10
1      5
1      3

we need to return only 1 10 i.e. leave the first lines for each user. So it is possible to make?

Possible duplicate of [How to select only the first rows for each unique value of a column](https://stackoverflow.com/questions/4662464/how-to-select-only-the-first-rows-for-each-unique-value-of-a-column) — Arnaud, Sep 21 '17 at 14:15

score 1 · Answer 1 · answered Sep 21 '17 at 14:16

1

You need to pick one id only, the one with max date right? You can run partition by, something like this:

select * from (
select  
logs.uid,
logs.event_value,
ROW_NUMBER() OVER (PARTITION BY uid ORDER BY first_event_time DESC) r 

from publicview.treasurehunters_ok logs
left join public.app_ok_cbaiodclebabababa_agg_users users  on 
logs.uid=users.uid
where  first_event_time>='01.09.2017 0:00:00'
and first_event_time <= '11.09.2017 0:00:00'
and logs.event ='balance_OK') tab
where r=1

answered Sep 21 '17 at 14:16

MiloBellano

396
1
5
15

Tnx, column reference "uid" is ambiguous. What's wrong – psysky Sep 21 '17 at 14:22
2

@D.Joe : Try `PARTITION BY logs.uid` or `PARTITION BY users.uid` . – Arnaud Sep 21 '17 at 14:24
yeah sorry PARTITION BY logs.uid ORDER BY first_event_time DESC – MiloBellano Sep 21 '17 at 14:25
1

The reason is because more than one table in the jon has a uid and you have to specify which one to use. So logs.uid is one from table logs. Hope explanation helps – MiloBellano Sep 21 '17 at 14:26
not max date, i need the first date where logs.event_value got the first value, so i am interested to know what the first value on logs.event_value has every user – psysky Sep 21 '17 at 14:27
1

so order asc (first date registered) instead. (PARTITION BY uid ORDER BY first_event_time ASC) – MiloBellano Sep 21 '17 at 19:50

Sort by time in SQL

1 Answers1