Selecting/Ranking Top Items

[BSosa]

Hello,

I have a question about writing an SQL query.

I have a table with the following fields (amongst others):

City
Store
SalesDollars

There are 10 cities, with each city containing 100+ stores, each with a SalesDollars numeric value.

1) How do I write a SELECT statement that will return the top 50 stores from each city (500 in total) ?

2) How do I create a field that is the ranking of all these stores in descending order by SalesDollars (1-500)?

Thank you in advance for your help!

Sam

 

[AlexCuse]

take a look here:

it was for SQL 2000 but I'm pretty sure its' ansi-compliant.

Just keep the count in your query if you want to show the rank.

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[r937]

nice article, alex -- too bad the white on light blue is so damned hard to read

i had to Ctrl-A, Ctrl-C, and Ctrl-V into a text editor to read it

adding an overall rank is an extra complication and not included in your article, yes?

r937.com | rudy.ca

 

[AlexCuse]

Yeah I have been struggling to pick a new color scheme for months :-( Any suggestions? I'm thinking of using a (very) dark green background, not sure for the fore. I'm gonna try to have it changed today or tomorrow now that I have heard 2 votes from the 'hard to read' camp.

The rank is included in an intermediate step, but not the final step. I forgot the HAVING clause was not present in the intermediate step...

BSosa - if you work through the example, keep this:

, count(b.DriverName)

In the query when you add your having clause (this will give you the rank).

Sorry about that.

Alex



[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[r937]

alex, he wants an overall rank

have fun

r937.com | rudy.ca