MySQL select one row, join multiple to it

Question

I have a table named sessions setup like this:

#################################
# set    # timestamp  # session #
#################################
# 5      # 1306458002 # 11      #
# 3      # 1306473234 # 6       #
# 3      # 1305241207 # 3       #
...
#################################

I have a second table named events setup like this:

#######################
# session    # code   #
#######################
# 6          # 45     #
# 6          # -10    #
# 6          # 0      #
# 3          # 7      #
...
#######################

I need to select the latest session of a given set, then join the associated event codes on top of that one record. Most importantly though, I just need this information from suppling a set (here the set is 3):

########
# code #
########
# 45   #
# -10  #
# 0    #
########

This should be really simple, I'm just having issues for some reason. Been a long day. — Ryan Turner
– Ryan Turner, Commented May 27, 2011 at 17:49
u need latest session or need all session list of given set? OR u want all code of that latest set? — xkeshav
– xkeshav, Commented May 27, 2011 at 17:50
I need all of the events associated with the latest instance of a given set. — Ryan Turner
– Ryan Turner, Commented May 27, 2011 at 18:11

GolezTrol · Accepted Answer · 2011-05-27 17:53:22Z

2

select
    e.code
from
    (select
        max(session) as session
    from
        sessions s
    where
        s.set = 3) ms
    inner join event e on e.session = ms.session

answered May 27, 2011 at 17:53

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

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

4 Comments

xkeshav Over a year ago

Good one ! +1 for using sub query

Ryan Turner Over a year ago

Perfect! Thanks. Now just gotta cross my fingers that sessions never go back in time, since it's sorting by session instead of by timestamp.

Ryan Turner Over a year ago

Took 0.005 sec to execute on my data, now to see how well the other queries hold up.

GolezTrol Over a year ago

@Ryan, sorry, I overlooked the timestamp. It's better not to take the risk of sorting by sessionid and use ryebr3ad's solution instead.

ryebr3ad · Accepted Answer · 2011-05-27 17:52:49Z

0

select e.code
from events e
inner join sessions s
    on s.session = e.session
where s.set = <whatever set you want>
and s.timestamp = (select max(timestamp) from sessions where set = s.set)

answered May 27, 2011 at 17:52

ryebr3ad

1,2483 gold badges12 silver badges20 bronze badges

8 Comments

xkeshav Over a year ago

why using WHERE clause in inner query??

ryebr3ad Over a year ago

So it knows to choose timestamps related to that particular set and not the one from, say, set 5 if 3 is chosen.

Ryan Turner Over a year ago

Works great, filters by the correct column (timestamp instead of session), but takes 0.024 seconds on my data. Now it's time to choose between fast/possibly inaccurate and slow/definitely correct.

ryebr3ad Over a year ago

I mean, typically I'd only use the where clause in the inner query, but with timestamps allowing possible duplicate values it seemed wise to filter the outer query for the set that was needed first, before diving into which timestamp to pull.

ryebr3ad Over a year ago

Us same-first-namers gotta stick together :-P

|

Collectives™ on Stack Overflow

MySQL select one row, join multiple to it

2 Answers 2

4 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related