Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Unanswered: Min/Maximum Grouping Query

    I know this has been posted before, but I can't find the previous threads so please bear with me....

    I want to grab the very 1st record of each product in a table like this
    Code:
    ID   CLIENTID   PRODID
    1     a                     1
    2     b                     1
    3     c                     1
    4     a                     2
    5     b                     2
    6     c                     2
    7     a                     3
    8     b                     3
    9     c                     3
    so that I'd get a record set like:
    Code:
    ID   CLIENTID   PRODID
    1     a                     1
    4     a                     2
    7     a                     3
    Thanks for the hellp guru's

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT t1.ID, t1.CLIENTID, t1.PRODID
    FROM table t1
    INNER JOIN (
    SELECT MIN(ID) AS ID, PRODID
    FROM table
    ORDER BY PRODID) t2 ON t1.ID = t2.ID
    AND t1.PRODID = t2.PRODID
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You did mean GROUP BY, not ORDER BY, right?

    SELECT t1.ID, t1.CLIENTID, t1.PRODID
    FROM table t1
    INNER JOIN (
    SELECT MIN(ID) AS ID, PRODID
    FROM table
    GROUP BY PRODID) t2 ON t1.ID = t2.ID
    AND t1.PRODID = t2.PRODID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Yep. Been sniped. (grin)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Thanks guys, exactly what I was after.

Posting Permissions

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