Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

RANK/TOP WITHOUT TIES Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 2:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
Hopefully someone can set me straight here - I am looking for a way to select the top n items, so long as the n+1th item does NOT have the same value as the nth item.

Basically, I'm looking for the opposite of:
SELECT TOP 5 WITH TIES col1
FROM Mytable
ORDER BY col1

If sorting all values of col1 would give these values: 5,4,3,2,1,1,1
... then the WITH TIES query above would return 7 rows...

I'm looking for a query that would return only 4 rows: 5,4,3,2
Since including all of the 1's would push it over the limit of n rows, I want to exclude ALL rows with this value, not just the one that happens to get sorted first.

Basically, I want the TOP n, except for when the TOP WITH TIES n will have more than n rows.

I hope I'm making sense here - as I feel there's got to be a clever way to do this that I'm just not seeing immediately.

Thanks,
Brigid

Post #1351272
Posted Tuesday, August 28, 2012 7:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
Look at the RANK() and DENSE_RANK() window functions.

http://msdn.microsoft.com/en-us/library/ms176102.aspx
http://msdn.microsoft.com/en-us/library/ms173825.aspx



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1351361
Posted Wednesday, August 29, 2012 7:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
dwain.c (8/28/2012)
Look at the RANK() and DENSE_RANK() window functions.


Not sure how RANK() OR DENSE_RANK() would help in this example. I thought a lot about the functions RANK, DENSE_RANK, and ROW_NUMBER and am hoping someone can see something clever that won't necessitate joining on the result of these functions...

These functions applied to the "col1" data I gave above would give:
col1 rank dense row_num
5 1 1 1
4 2 2 2
3 3 3 3
2 4 4 4
1 5 5 5
1 5 5 6
1 5 5 7

So basically, I would like something that says something like:
select where rank <= 5 and the max row_num associated with that rank is also <= 5

Is there a way to set the assignment of the rank function? So that instead of setting all the tied values to the next integer, it would set it to the maximum row number for that value?

My ideal MYRANK function would give
col1 myrank
5 1
4 2
3 3
2 4
1 7
1 7
1 7

Then I could just SELECT col1 WHERE myrank <= 5.

I feel like there's got to be a simple explanation that I'm just not seeing... all help is greatly appreciated!
Post #1351600
Posted Wednesday, August 29, 2012 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
This is pretty simple using Row_Number. Please notice how I posted sample data to make this a lot easier for anybody else. You should do something similar in your future posts.

;with MyRank (col1)
as
(
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 1 union all
select 1 union all
select 1
)

select col1, MAX(RowNum) as MaxRowNum
from
(
select col1, ROW_NUMBER() over (order by col1 desc) as RowNum
from MyRank
) x
group by col1
having Max(RowNum) <= 5
order by col1 desc

Or for those who would prefer cacading ctes, you can modify it like this.

;with MyRank (col1)
as
(
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 1 union all
select 1 union all
select 1
)
, MaxRank as
(
select col1, ROW_NUMBER() over (order by col1 desc) as RowNum
from MyRank
)

select col1, MAX(RowNum) as MaxRowNum
from MaxRank
group by col1
having Max(RowNum) <= 5
order by col1 desc



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1351624
Posted Wednesday, August 29, 2012 8:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
Another option:

;with MyRank (col1)
as
(
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 1 union all
select 1 union all
select 1
)
SELECT TOP 5 col1
FROM MyRank
GROUP BY col1
HAVING COUNT(*) = 1
ORDER BY col1




Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351643
Posted Wednesday, August 29, 2012 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
Luis Cazares (8/29/2012)
Another option:

;with MyRank (col1)
as
(
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 1 union all
select 1 union all
select 1
)
SELECT TOP 5 col1
FROM MyRank
GROUP BY col1
HAVING COUNT(*) = 1
ORDER BY col1



That doesn't quite meet the requirements. If for example they wanted to top 7 this code would not work because it would not return a row where col1 = 1.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1351644
Posted Wednesday, August 29, 2012 8:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
I tested my last solution and won't give the exact results.
Try this instead:
;with MyRank (col1)
as
(
select 6 union all
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 2 union all
select 1
), MyRank2
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
)
SELECT col1
FROM MyRank2
WHERE RCount = 1





Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351648
Posted Wednesday, August 29, 2012 8:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
Luis Cazares (8/29/2012)
I tested my last solution and won't give the exact results.
Try this instead:
;with MyRank (col1)
as
(
select 6 union all
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 2 union all
select 1
), MyRank2
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
)
SELECT col1
FROM MyRank2
WHERE RCount = 1




Still not quite right. You have a TOP but not Order By. Which top 5 will it get?

Adding the order by seems to get it though.

;with MyRank (col1)
as
(
select 6 union all
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 2 union all
select 1
), MyRank2
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
order by col1 desc --Need this order by to ensure which top 5
)

SELECT col1
FROM MyRank2
WHERE RCount = 1

This seems to have a slightly simpler execution plan than mine too. We have certainly proven there is more than 1 way to do this. I am sure somebody will come along with another one within a couple hours.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1351656
Posted Wednesday, August 29, 2012 8:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
Sean Lange (8/29/2012)
Luis Cazares (8/29/2012)
I tested my last solution and won't give the exact results.
Try this instead:
;with MyRank (col1)
as
(
select 6 union all
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 2 union all
select 1
), MyRank2
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
)
SELECT col1
FROM MyRank2
WHERE RCount = 1




Still not quite right. You have a TOP but not Order By. Which top 5 will it get?

Adding the order by seems to get it though.

;with MyRank (col1)
as
(
select 6 union all
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 2 union all
select 1
), MyRank2
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
order by col1 desc --Need this order by to ensure which top 5
)

SELECT col1
FROM MyRank2
WHERE RCount = 1

This seems to have a slightly simpler execution plan than mine too. We have certainly proven there is more than 1 way to do this. I am sure somebody will come along with another one within a couple hours.


What does the OP want with the following?

;with MyRank (col1)
as
(
select 6 union all
select 5 union all
select 5 union all
select 4 union all
select 3 union all
select 2 union all
select 1
), MyRank2
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
order by col1 desc --Need this order by to ensure which top 5
)

SELECT col1
FROM MyRank2
WHERE RCount = 1




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1351662
Posted Wednesday, August 29, 2012 8:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
I had the idea that the group by would do the sort. However, it might not be the safest thing to do it.
Your query gave me some incorrect results when I change the values.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351664
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse