single highest number - SQL Server Forums

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

single highest number

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2008-10-13 : 12:25:55

if i have just a generic table how would I bring back just the highest number and say name associated with it so IE

Column 1 = name and column 2 = number

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 12:35:34

select col1,col2 from table where col2=(select max(col2) from table)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 12:46:08

select top 1 name,number from table order by number desc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 12:58:21

Above query will give you one row even if there were more than one names having highest number. The other query will list you all names. Pick your choice, depending on what you want.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 13:00:27

quote:
Originally posted by sakets_2000

Above query will give you one row even if there were more than one names having highest number. The other query will list you all names. Pick your choice, depending on what you want.

yeah thats true. however you could use with ties option if you're using sql 2005

select top (1) with ties name,number from table order by number desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 14:18:20

I think WITH TIES is available for SQL Server 2000 also.

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 15:40:53

"with ties" isn't valid in sql server 2k.

But when I write it in analyzer, it marks it in blue. strange !!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 16:07:17

with ties works in Sql server 2000.


declare @mytab table (col1 varchar(5), col2 int)

insert into @mytab 
select 'A', 1
union all
select 'B', 1
union all
select 'C', 2
union all
select 'D', 3
union all
select 'E', 3

select top 1 with ties col1, col2 from @mytab order by col2 desc

col1  col2        
----- ----------- 
D     3
E     3

(2 row(s) affected)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 16:11:59

Why would nothing in the knowledge docs along with installation mention anything about "with ties".
The above is working fine though.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 16:18:22

WITH TIES is in SELECT Clause Help

http://msdn.microsoft.com/en-us/library/aa259187(SQL.80).aspx#_select_clause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources