SQL Server Performance Forum – Threads Archive

top row from table

Hi guys, I have one table called gate and its contains rows like id sid order
1 2 30
1 4 20
1 7 10
2 2 40
2 8 30
2 9 15
3 5 50
3 7 34
3 8 21 I want to select first row of each id. please help me how to do it. Thank in advance.. karthik
lkarthik

Select * from yourtable T
where order=(select Top 1 order from yourtable where id=T.id) Madhivanan Failing to plan is Planning to fail

Sir wat could b the other way of doing the same thing

just a slight addition
Select * from yourtable T
where order=(select Top 1 order from yourtable where id=T.id ORDER BY whatever_you_want) Apart from this, what’s wrong with Madhivanan’s suggestion? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan Select * from yourtable T where order=(select Top 1 order from yourtable where id=T.id) Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> Did you get the expected result? Otherwise post expected result There may be other methods. But my suggested one may be effecient [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

yeah got the expected result i just want to know the alternative method for this if any
Thanks

Well. I thought you are the questioner who opened this thread [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

If you were looking for either the top Sid, or the top Order, for each Id, then perhaps this is the alternative you’re looking for: SELECT T.Id,
MIN(T.Sid) LowestSid, MAX(T.Sid) HighestSid,
MIN(T.Order) LowestOrder, MAX(T.Order) HighestOrder
FROM dbo.gate T
GROUP BY T.Id Use either MIN or MAX, depending what you understand by "top". But you’re probably looking for the first row, so look at Frank’s solution.

]]>