Query Problem

datune · #1 (**permalink**) 02-06-10, 10:40

Hello,

I am struggling trying to achieve the following:

Table has 4 columns:
id
session_id
x
y

5 Sample records
1, 1, 10, 20
2, 1, 5, 10
3, 1, 2, 4
4, 2, 5, 10
5, 2, 2, 4

SELECT t1.id, t1.session_id, t1.diff FROM (SELECT id, session_id, y-x as diffFROM poker_sessions) as t1 WHERE MAX(t1.diff)

What I want is to have the maximum difference PER session_id

So I expect two records:

1, 1, 10 (ten is the highest difference)
4, 2, 5 (five being the highest difference for session_id 2)

I can't seem to figure out how to do that. I would really appreciate some help, I have tried everything I can think off, to no avail. Thanks!

r937 · #2 (**permalink**) 02-06-10, 10:57

Code:

SELECT t.id
     , t.session_id
     , t.y - t.x AS diff
  FROM ( SELECT session_id 
              , MAX(y-x) AS max_diff
           FROM poker_sessions
         GROUP
             BY session_id ) AS m
INNER
  JOIN poker_sessions AS t
    ON t.session_id = m.session_id
   AND t.y - t.x = m.max_diff

datune · #3 (**permalink**) 02-06-10, 11:06

Nice r937!

Thanks a lot!

Internet Services

Web Development

Digital Marketing

Consumer Tech