get row with highest value from field in list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get row with highest value from field in list

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-03-27 : 09:05:46

in the list below, I use something like this
select * from mytable where codenum=E4555 order by seller_level

Now, I want only the row that has the highest seller_level


bookid  codenum locid   s_num  seller_level
2323	E4555	00	R1234	5
3455	E4555	00	T4566	3
4566	E4555	00	R3355	2
3344	E4555	00	A3333	1

[edited to add]
ok, it must be early and my brain is snoozing

since I already ordered the list, just do a top 1
ie. select top 1 * ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 10:02:30

SELECT BookID, CodeNum, LocID, s_num, Seller_Level
FROM (
SELECT BookID, CodeNum, LocID, s_num, Seller_Level, ROW_NUMBER() OVER (PARTITION BY CodeNum ORDER BY Seller_Level DESC) AS RecID
) AS x
WHERE RecID = 1

will get you the wanted record for all codenums at once.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources