top row from table | SQL Server Performance Forums

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

Select * from yourtable T
where order=(select Top 1 order from yourtable where 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 ORDER BY whatever_you_want) Apart from this, what’s wrong with Madhivanan’s suggestion? —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?
Ich unterstuetze PASS Deutschland e.V.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><br />Select * from yourtable T<br />where order=(select Top 1 order from yourtable where<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Did you get the expected result?<br />Otherwise post expected result<br />There may be other methods. But my suggested one may be effecient [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

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

Well. I thought you are the questioner who opened this thread [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Madhivanan<br /><br />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.