Ranking\top orders - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Ranking\top orders

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-21 : 08:25:06

Eric Saint-Marc writes "Hello Guru

I would like to know the better way to generate a ranking/identity column based on maximun value.
Take the orders/orders_details table from northwind.
The query should show which customer received the most items for a single order, sorter by quantity and by date.We also need to have a column called ranking in which the customer will be ranked from 1 to ....
In short the query should return the following fields, customerID, OrderDate, Quantity and ranking.

Thanks in advance."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-21 : 08:27:42

create table #temp(customerID int, OrderDate datetime, Quantity numeric(12,2), ranking int identity(1,1))
go
insert into #temp (customerID, OrderDate, Quantity)
select customerID, OrderDate, Quantity
from MyTable
order by Quantity desc, OrderDate desc

select * from #temp
go
drop table #temp

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources