Results 1 to 3 of 3

Thread: Query Problem

  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Question Unanswered: Query Problem

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    Nice r937!

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •