Select ID with the Greatest Rank

Question

Post reply

Select ID with the Greatest Rank

caippers

Mr or Mrs. 500

Points: 582
More actions
April 29, 2014 at 9:50 am

#292841

I have a table that contains data in the following format:
Here is an example of the table:
ID Source Rank
152 Foo 10
152 Bar 20
How could I query this table to grab the ID with the lowest rank? ID is the PK in this table.
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

SELECT TOP 1 ID

FROM

ORDER BY Rank ASC

What about ties (ie two PK with the same Rank)

Far away is close at hand in the images of elsewhere.
Anon.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

caippers (4/29/2014)
ID is the PK in this table.

If ID is the PK, why is it repeated? PKs should be unique.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Luis Cazares (4/29/2014)
caippers (4/29/2014)
ID is the PK in this table.
If ID is the PK, why is it repeated? PKs should be unique.

I noticed that but declined to comment on the obvious 😀

Far away is close at hand in the images of elsewhere.
Anon.

caippers Mr or Mrs. 500 Points: 582 More actions · Answer 4

Luis Cazares (4/29/2014)
caippers (4/29/2014)
ID is the PK in this table.
If ID is the PK, why is it repeated? PKs should be unique.

My apologizes. I misspoke. It is not the primary key.

I will never have two ids come in with the same rank.

Thanks for your post but when I tried this I only received the top 1 from the entire result set.

I have multiple ID's in the table:

ID Source Rank

123 Foo 10

123 Bar 20

124 Fee 30

124 Fine 40

David Burrows SSC Guru Points: 65142 More actions · Answer 5

caippers (4/29/2014)

Thanks for your post but when I tried this I only received the top 1 from the entire result set.

That is what you asked for.

What is the expect result from the data in your last post.

Far away is close at hand in the images of elsewhere.
Anon.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Something like this?

WITH SampleData(ID, Source, Rank) AS(

SELECT 123, 'Foo', 10 UNION ALL

SELECT 123, 'Bar', 20 UNION ALL

SELECT 124, 'Fee', 30 UNION ALL

SELECT 124, 'Fine', 40

)

SELECT *

FROM SampleData

WHERE ID IN(SELECT TOP 1 ID

FROM SampleData

ORDER BY Rank)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

caippers Mr or Mrs. 500 Points: 582 More actions · Answer 7

Luis Cazares (4/29/2014)
Something like this?
WITH SampleData(ID, Source, Rank) AS(
SELECT 123, 'Foo', 10 UNION ALL
SELECT 123, 'Bar', 20 UNION ALL
SELECT 124, 'Fee', 30 UNION ALL
SELECT 124, 'Fine', 40
)
SELECT *
FROM SampleData
WHERE ID IN(SELECT TOP 1 ID
FROM SampleData
ORDER BY Rank)

This is exactly what I'm looking for! Your help is greatly appreciated.

David Burrows SSC Guru Points: 65142 More actions · Answer 8

Yep Luis always gives good answers 😉

Nice one Luis, especially from sparse details 😀

Far away is close at hand in the images of elsewhere.
Anon.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Here is the code provided by Luis and my shot at it as well:

create table #testdata(

ID int,

Source varchar(10),

RankVal int);

go

insert into #testdata

SELECT 123, 'Foo', 10 UNION ALL

SELECT 123, 'Bar', 20 UNION ALL

SELECT 124, 'Fee', 30 UNION ALL

SELECT 124, 'Fine', 40

;

go

SELECT *

FROM #testdata

WHERE ID IN(SELECT TOP 1 ID

FROM #testdata

ORDER BY RankVal);

go

with basedata as (

select

ID,

Source,

RankVal,

rn = row_number() over (partition by ID order by RankVal)

from

#testdata

)

select * from basedata where rn = 1;

go

caippers Mr or Mrs. 500 Points: 582 More actions · Answer 10

I apologize for that. I will ensure that I provide more info in the future.

caippers Mr or Mrs. 500 Points: 582 More actions · Answer 11

caippers

Mr or Mrs. 500

Points: 582

April 29, 2014 at 10:28 am

#1709559

Thank you as well!

David Burrows SSC Guru Points: 65142 More actions · Answer 12

caippers (4/29/2014)
I apologize for that. I will ensure that I provide more info in the future.

It was not intended as a criticism :blush:

See links in Lynn's signature for information 🙂

Far away is close at hand in the images of elsewhere.
Anon.