Thread: Sql Group By

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    1
    Rep Power
    0

    Sql Group By


    Hi, I have a table like this:

    ID Name
    01 Billy Thompson
    02 Billy Thompson
    03 Sam Smiler
    04 Billy Thompson
    05 Theresa Smith
    06 Theresa Smith
    07 Sam Smiler
    08 Billy Thompson
    09 Sam Smiler
    10 Sam Smiler

    I need to produce a record set GROUPED BY Name, but that also contains an ID number for that name, e.g.:

    01 Billy Thompson
    03 Sam Smiler
    05 Theresa Smith

    Billy Thompson has four different ID numbers, I don't care which ID number is returned, so long as I get one of his ID numbers.

    I've tried using the following SQL statements:

    SELECT ID, Name FROM Table1 GROUP BY Name
    SELECT * FROM Table1 GROUP BY Name

    Neither of these work. I can get around the problem by opening one recordset with:

    SELECT Name FROM Table1 GROUP BY Name

    And using a For...Next loop (in ASP) then using a:

    SELECT ID FROM Table1 WHERE Name = RS("Name")

    ...within the loop, but I'd prefer to let the database do this work and just give me what I want.

    Any ideas?

    Little Charva
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Code:
    select min(id),name from t
    group by name

IMN logo majestic logo threadwatch logo seochat tools logo