Results 1 to 4 of 4

Thread: group by

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: group by

    id name address

    1 rocket xxxx
    2 scooter
    3 bus 5555
    2 bike 8888
    3 bus 999
    4 road 6666
    4 7777
    1 canal yyyy

    I want to write a query to get the result from the above data as shown below

    id name address
    1 rocket xxxx
    2 scooter 8888
    3 bus 5555
    4 road 6666


    i should get the first non-nul value of name and address group by id.

  2. #2
    Join Date
    Mar 2004
    Posts
    8
    Assuming you name your table as tblData, the following will work:

    SELECT DISTINCT D.[id],
    (SELECT TOP 1 name FROM tblData WHERE [id] = D.[id] AND name IS NOT NULL) AS name,
    (SELECT TOP 1 address FROM tblData WHERE [id] = D.[id] AND address IS NOT NULL) AS address
    FROM tblData D
    ORDER BY D.[id]

    It may not be the prettiest solution though...

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    SELECT id, MIN(name) name, MIN(address)
    FROM table
    GROUP BY id
    ORDER BY id

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "first non-nul value of name and address group by id"???

    There is no "first" value for an ID unless you specify additional sort orders that define a unique composite key. If you just want the first value alphabetically, RogerWilco's simple solution will work fine. If you have in mind some different sort logic, or you are relying upon the order in which the data is stored in the table, then you need to rethink your design.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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