Help on Writing Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help on Writing Query

Hi, I have a table like below: id, value
1 a
1 b
1 c
2 x
2 y
2 z
3 i
3 j
3 k I want to retreive results as below id, value
1 a
2 x
3 i Can anyone suggest me how to write a query for this? Urgent pls Regards,
Balasundaram
Balasundaram,<br /><br />This forum does not usually answer questions about basic T-SQL syntax, but you’re in luck![<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />SELECT id, MIN(value)<br />FROM my_table<br />GROUP BY id<br /><br />Lookup SELECT in Books Online – this is really one of the first things you learn when learning any query language.
Hi Adriaan, Thanks for the reply. I want the first(top) value not min value. I think logically it is NOT possible, but still want try with you guys. Any suggestions to join or dumping into temp tables? id, value
1 a
2 x
3 i Regards,
Balasundaram
Bala,
Adriann is correct min will give you the answer that you are loking for .
try it
What is the "first" value depends entirely on how you define the order of the information. If the order that you need is by a third column in the table, for instance a date, and you want to return the value for the entry with the earliest date, then you can use a correlated subquery like this: SELECT id, value
FROM mytable
WHERE mydate = (SELECT MIN(tmp.mydate) FROM mytable AS tmp WHERE tmp.id = mytable.id) But you have to be aware that if there are multiple records with the same id and the exact same date, then the query will return multiple rows per id. If you have an IDENTITY column recording the order in which the data was entered, then you can rewrite the correlated subquery to use that column.
Adding to Adriaan, you can also have an identity field, so that you can use SELECT id, value
FROM mytable
WHERE identityFiled in (SELECT MIN(identityFiled) FROM mytable group by id)
Madhivanan Failing to plan is Planning to fail
]]>