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.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...|||SELECT id, MIN(name) name, MIN(address)
FROM table
GROUP BY id
ORDER BY id|||"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.