How to select row with maximum value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How to select row with maximum value?

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-11-11 : 11:48:27

How would I write this T-SQL statement correctly?

SELECT Name, Badge
FROM Employees
WHERE Max(ID)

I know the script above is wrong; in there WHERE clause, I want to return the greatest ID number.

Avoid Sears Home Improvement

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-11 : 12:23:15

Try this:

SELECT Name, Badge
FROM Employees
WHERE ID = (select Max(ID) from Employees)

Terry

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 12:31:08

Assume ID is primary key:

SELECT TOP 1 WITH TIES
Name, Badge
From Employees
order by ID desc

readysetstop
Posting Yak Master

123 Posts

Posted - 2008-11-11 : 12:34:46

Another way:


SELECT TOP 1 Name, Badge
FROM Employees
ORDER BY ID DESC

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-11 : 12:41:23

quote:
Originally posted by hanbingl

Assume ID is primary key:

SELECT TOP 1 WITH TIES
Name, Badge
From Employees
order by ID desc

Does this work in SQL 2000?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 13:09:34

yup, i personally tested

quote:
Originally posted by sodeep

quote:
Originally posted by hanbingl

Assume ID is primary key:

SELECT TOP 1 WITH TIES
Name, Badge
From Employees
order by ID desc

Does this work in SQL 2000?

jp2code
Posting Yak Master

175 Posts

Posted - 2008-11-11 : 13:14:31

Thanks!

What's the difference between "WITH TIES" and the version without?

Avoid Sears Home Improvement

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 13:32:07

Say if you have two identical IDs with same Name but different Badges, WITH TIES will show both, without it, only one.
If ID is unique, you can go without WITH TIES, if ID can be dups it is save to use WITH TIES


ID      NAME        BADGE
-----   ---------   ---------
10      Hanbing     Red
10      Hanbing     Blue
9       JP2CODE     Red

Select top 1 WITH TIES:

ID      NAME        BADGE
-----   ---------   ---------
10      Hanbing     Red
10      Hanbing     Blue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources