1

so i have a table for user sessions w/ both stamp_in and stamp_out (epoch) for each session 

TABLE:    tk_sessions 
columns:  ses_id | ass_id | stamp_in | stamp_out

(ass_id stands for asset ID hehe)

i basically just want to retrieve the latest sessions for each asset...

So i tried this:

SELECT ses_id,ass_id,stamp_in,stamp_out 
    FROM tk_sessions 
    ORDER BY stamp_in DESC

that returns ALL the sessions in the expected descending order I just want to return 1 of each ass_id so i added the GROUP BY ass_id statement

SELECT ses_id,ass_id,stamp_in,stamp_out 
    FROM tk_sessions GROUP BY ass_id 
    ORDER BY stamp_in DESC

but the results are odd... this seems to return 1 row for each ass_id as expected, but are returning the wrong sessions (not the latest stamp_in sessions)

What gives?

scratches head

BrownChiLD
  • 3,545
  • 9
  • 43
  • 61
  • Is this what you are looking for: `SELECT ses_id,ass_id, MAX(stamp_in), stamp_out FROM tk_sessions GROUP BY ass_id` – younis Aug 29 '14 at 12:57

1 Answers1

1

Group by in absence of aggregate function will result in indeterminate order for your query you can use a self join by calculating the max value of stamp_in and join with your table so the row with highest stamp_in value will be returned for each of your asset id

SELECT s.*
 FROM tk_sessions s
JOIN (SELECT MAX(stamp_in) stamp_in ,ass_id FROM tk_sessions  GROUP BY ass_id ) t
ON(s.stamp_in = t.stamp_in AND s.ass_id = t.ass_id)
 ORDER BY s.stamp_in DESC

Reference :The Rows Holding the Group-wise Maximum of a Certain Column

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • oohhh , ok .. i did try this a while ago and for some reason it returned similar results to my 2nd attempt where it was returning the wrong columns.. but i copy pasted your example and it seemed to work.. i'll study this, thanks mate! – BrownChiLD Aug 29 '14 at 12:46
  • @BrownChiLD you are welocme you can mark this answer as accepted – M Khalid Junaid Aug 29 '14 at 13:06