Postgres order by 1st event in group of events

RobCrew 0
Viewed 36

I do a lot of looking at user behavior and want an easy way to order by the first event in a group of events per user. i.e.

User1 | Event1
User1 | Event2
User1 | Event3
User2 | Event1
User2 | Event2
User2 | Event3

Where Event1 for User1 is earlier than Event1 for User2.

I know I could make a table with each users' first event and then join it, but I feel like there is a simpler way I am just not seeing. Any ideas?

sql postgresql
2 Answers

Could be as simple as this if I understand correctly:

SELECT * 
FROM your_table_name
ORDER BY user_field, event_field;

Or try the ORDER BY in reversed if you want all the Event 1s first

SELECT * 
FROM your_table_name
ORDER BY event_field, user_field;
FishMan0

You could use the first_value window function, but in my experience it would probably be slower than the join to the derived table.

select userid, event from foo order by first_value(event) over (partition by userid order by something)

In case you didn't know, you can do the join to a dynamically defined aggregate table, it doesn't have to be a permanent table.

It would be easier to offer example queries if your example data had included things like column names, or even all the necessary example columns.

jjanes0
Related