Results 1 to 7 of 7

Thread: Top values

  1. #1
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39

    Unanswered: Top values

    How can I get the Top n values for groups of records?
    For example:

    UserID OrderNum
    1 12
    1 14
    1 16
    2 3
    2 4
    2 5
    3 56
    3 57
    3 58

    How can I return the Top 2 values giving:

    UserID OrderNum
    1 12
    1 14
    2 3
    2 4
    3 56
    3 57

    and can I enter the Top value criteria somewhere in the SQL grid ?

    And finally, is it possible to use one query to select one of several
    Top values depending on a value in a table's field?
    If the field value is 2, Top 2, field value is 3, Top 3, etc.?

    TIA Eric

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query to do what you want is

    select UserID, OrderNum
    from yourtable X
    where OrderNum in
    ( select top 2
    OrderNum
    from yourtable
    where UserID = X.UserID
    order by OrderNum asc )
    order by UserID, OrderNum

    dunno how to make the "2" a variable, sorry

    replacing it with [enter top number] doesn't seem to work


    rudy

  3. #3
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39
    Hi Rudy,

    I tried your suggestion but it's not working out at present.
    It's only returning the top 2 for one UserID.

    TIA Eric

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can i see the query you ran?

  5. #5
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39
    Here it is Rudy,

    SELECT Detentions.StudentID, Detentions.DetID
    FROM Detentions
    WHERE (((Detentions.DetID) In (select top 2
    DetID
    from Detentions
    where StudentID = Detentions.StudentID
    order by DetID asc)))
    ORDER BY Detentions.StudentID, Detentions.DetID;

    Thanks! Eric

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you removed/obscured the correlation variable

    the subquery no longer refers to the outer query -- in effect, it returns the top 2 overall

    change it to this:

    SELECT StudentID, DetID
    FROM Detentions X
    WHERE DetID In
    (select top 2 DetID
    from Detentions
    where StudentID = X.StudentID
    order by DetID asc)))
    ORDER BY StudentID, DetID;


    rudy

  7. #7
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39
    Thanks very much Rudy, I have it working now!
    I removed the X's because I thought you had included them as part of the name, like TableX.

    I really appreciate your help! This is great.

    Eric

Posting Permissions

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