How do I query only max value columns in table

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

General SQL Server Forums

New to SQL Server Programming

How do I query only max value columns in table

Author

Topic

jimjenkins69
Starting Member

1 Post

Posted - 2007-12-26 : 15:06:54

I just want to query all records with the maximum value in column invoices.numbers. So for instance I have records with invoice numbers = 850, 849, 800 respectively. I want only records 850 (the highest value). Then next month I want invoices with value 851. Anyone know how to write this easily so I don't have to write it out everytime, e.g. invoice.numbers=851?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 15:08:17

SELECT * FROM invoice WHERE numbers = (SELECT MAX(numbers) FROM invoice)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-26 : 15:58:36

SELECT TOP 1 WITH TIES * FROM Invoice ORDER BY Numbers DESC

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-27 : 04:15:50

and other way, if you use SQL Server 2005, is

select columns from
(
select row_number() over(partition by numbers order by numbers desc) as row_num,columns from table
) as t
where row_num=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources