Simple SQL Reverse Count pull?? 1

[kermitforney]

Not sure what this type of query would be called. I apologize if the title is confusing.

Gist:
I need to exclude the top count of rows from a query. The example below is to better illustrate this.

Example:

Table Data

ID, Rank, ItemDesc

1, 1, Basketballs
2, 2, Soccer balls
3, 3, Baseballs
4, 4, Volleyballs

I need to be able to select the "bottom" two items, maybe this would be called a reverse top?

 

[RiverGuy]

Use TOP 2, just order descending.

 

[kermitforney]

Crud, I forgot to include that the amount of rows would be dynamic and based on a value in the table.

 

[kermitforney]

I should have given a more thorough example.

Rank, Type, ItemDesc
1, 1, Basketballs
2, 1, Soccer balls
3, 1, Baseballs
4, 1, Volleyballs
1, 2, Hoop
2, 2, Goal
3, 2, Bat
4, 2, Net

I need to select the bottom two from Type 1 followed by the top three in Type 2, but return them in the same result set. I am thinking there is no other way to get around this without looping through the set via cursor, but I have never used a cursor. I'll keep googling, but if you guys have any insight let me know.

Thanks in advance.

 

[RiverGuy]

There's not really an automatic way to get 2 from Type 1 and 3 from Type 2, but this will get 3 from both:

SELECT * FROM 
	(SELECT [Rank], [Type], ItemDesc,
	RANK() OVER (PARTITION BY [Type] ORDER BY [Rank] DESC) AS RNK  
	FROM YourTable) x
WHERE RNK BETWEEN 1 AND 3

 

[gmmastros]

You could separate this in to 2 queries with a union all.

Select Top 2 *
From   YourTable
Where  Type = 1
Order By Rank DESC

Union All

Select Top 3 *
From   YourTable
Where  Type = 2
Order BY Rank DESC

This is untested, but should work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kermitforney]

RiverGuy, FTW!!!!!!!

The partition by and rank functions just saved my ass. If I could give you 54,000 stars I would!

Thank you sooooo much!!!