Ranking multiple columns - 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

Transact-SQL (2000)

Ranking multiple columns

Author

Topic

mem
Starting Member

28 Posts

Posted - 2004-10-19 : 22:10:58

Hello,

I'm trying to rank for each column or 'category' in a recordset. For example, Jane doe was number 1 in shoes, and John Wayne was number 1 in watches. And if the top person for shoes is also the top person for watches then show that person twice with each value. Finally, I only want to see the top 1 person for each category.

Here's sample ddl, etc...

CREATE TABLE reps
(
rep_id int IDENTITY NOT NULL PRIMARY KEY,
rep_name varchar(25) NOT NULL
)
go

INSERT reps VALUES ('dumb dumb')
INSERT reps VALUES ('john wayne')
INSERT reps VALUES ('jane doe')
INSERT reps VALUES ('some guy')
go

CREATE TABLE Transactions
(
tran_id int IDENTITY NOT NULL PRIMARY KEY,
rep_id int NOT NULL,
tran_acc int NOT NULL,
tran_data int NOT NULL,
tran_ndata int NOT NULL
)
go

INSERT Transactions VALUES (100,1,1,3)
INSERT Transactions VALUES (101,2,5,7)
INSERT Transactions VALUES (100,4,3,3)
INSERT Transactions VALUES (102,7,25,30)
go

Results needed...

repname | values
======================
dumb dumb | 5 <---number 1 in tran_acc 'category'
john wayne| 25 <---number 1 in tran_data 'category'
jane doe | 30 <---number 1 in tran_ndata 'category'

I guess this could be a pivot table effect like so....

category | dumb dumb | john wayne | jane doe
===============================================
tran_acc | 5 | null | null
tran_data | null | 25 | null
tran_ndata | null | null | 30

Any help would be appreciated.

a_shyam41
Starting Member

9 Posts

Posted - 2004-10-20 : 05:28:27

are you looking for SQL statement(s) to achieve this???

mem
Starting Member

28 Posts

Posted - 2004-10-20 : 13:01:43

yes some sql would be appreciated.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-20 : 14:09:32

This do?


CREATE TABLE #reps
(
rep_id int IDENTITY(100,1) NOT NULL PRIMARY KEY,
rep_name varchar(25) NOT NULL
)

INSERT #reps VALUES ('dumb dumb')
INSERT #reps VALUES ('john wayne')
INSERT #reps VALUES ('jane doe')
INSERT #reps VALUES ('some guy')

CREATE TABLE #Transactions
(
tran_id int IDENTITY(100,1) NOT NULL PRIMARY KEY,
rep_id int NOT NULL,
tran_acc int NOT NULL,
tran_data int NOT NULL,
tran_ndata int NOT NULL
)

INSERT #Transactions VALUES (100,1,1,3)
INSERT #Transactions VALUES (101,5,7,7)
INSERT #Transactions VALUES (100,4,3,3)
INSERT #Transactions VALUES (102,2,25,30)

--Select * From #reps
--Select * From #Transactions

Select rep_Name, category = 'tran_acc', tran_acc From #transactions A Inner Join #reps B On A.rep_Id = B.rep_Id Where 0 = (Select count(*) From #transactions Where tran_acc > A.tran_acc)
Union
Select rep_Name, category = 'tran_data', tran_data From #transactions A Inner Join #reps B On A.rep_Id = B.rep_Id Where 0 = (Select count(*) From #transactions Where tran_data > A.tran_data)
Union
Select rep_Name, category = 'tran_ndata', tran_ndata From #transactions A Inner Join #reps B On A.rep_Id = B.rep_Id Where 0 = (Select count(*) From #transactions Where tran_ndata > A.tran_ndata)

Drop Table #reps
Drop Table #Transactions

Corey

mem
Starting Member

28 Posts

Posted - 2004-10-20 : 17:22:45

Not exactly, but that's my fault for not explaining correctly. Your suggestion got the ball rolling. I figured it out. Thanks Corey.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources