Ranking with Multiple Column

Question

Ranking with Multiple Column

prakashdotc

Ten Centuries

Points: 1165
More actions
July 23, 2009 at 7:00 am

#95879

Hi All,
I have a Table with the data as follows
----------------------------------------------------------------------
entity-------- paymethod -------- Stock-type -------- RANK
----------------------------------------------------------------------
NA-------- Budget --------OPEN-------- 1
NA-------- Budget --------OPEN-------- 1
NA-------- Budget --------OPEN-------- 1
CE -------- XIGN-------- OPEN -------- 2
CE -------- XIGN-------- OPEN -------- 2
OB -------- TERMS -------- OPEN -------- 3
NA-------- OTHER -------- OPEN -------- 4
NA-------- OTHER -------- OPEN -------- 4
NA-------- OTHER -------- OPEN -------- 4
NA-------- Budget -------- PAID -------- 5
NA-------- Budget -------- OPEN -------- 1
----------------------------------------------------------------------
Here entity, Paymethod, stock-type are the combination of the columns based on which I have the generate RANK as specified in the RANK Column
can any one help in this requirement
thanks in advance
Prakash.C

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I’m sorry, but I don’t understand what you need. There is a good chance that there are other readers that wanted to help you, but didn’t understand what you need. Pleas take few minutes and read the article that is referenced in my signature about how to post a question in a better way that will help you get answer for your questions.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

What is the basis for the ranking? It doesn't look like it can be # of payments because the one you have with RANK 4 in your example has more entries than the ones you have at RANKs 2 & 3.

You should be able to use the RANK() function, but I can't be specific because I don't have enough information. As Adi already mentioned if you post with more detail as the article referred to suggests then we can really help, and the exercise may actually help you determine how to accomplish the task, it has for me a few times.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Eddie Wuerch SSChampion Points: 12537 More actions · Answer 3

DENSE_RANK(), without a PARTITION BY clause will get you the grouping you're looking for, but how to pull off the order of the ranking in your limited sample will require more detail from you.

This will get you close, without the specific ordering you posted:

-- prep sample table

DECLARE @blah table(entity char(2) NOT NULL, paymethod varchar(10) NOT NULL, [stock-type] char(4) NOT NULL)

INSERT @blah (entity, paymethod, [stock-type])

SELECT 'NA', 'Budget', 'OPEN' UNION ALL SELECT

'NA', 'Budget', 'OPEN' UNION ALL SELECT

'CE', 'XIGN', 'OPEN' UNION ALL SELECT

'OB', 'TERMS', 'OPEN' UNION ALL SELECT

'NA', 'OTHER', 'OPEN' UNION ALL SELECT

'NA', 'Budget', 'PAID' UNION ALL SELECT

'NA', 'Budget', 'OPEN'

--

-- The SELECT ...DENSE_RANK() statement

SELECT entity, paymethod, [stock-type],

DENSE_RANK() OVER (ORDER BY [stock-type], paymethod, entity)

FROM @blah

Eddie Wuerch
MCM: SQL

prakashdotc Ten Centuries Points: 1165 More actions · Answer 4

Thank u Eddie Wuerch,

I have completed the script which is similar to u, thanks for u'r replay

SELECT SI.SINumber, (DENSE_RANK() OVER (ORDER BY RoleID, PaymentTypeID, (I.OpenStock&I.PaidStock))) AS RANK

FROM

SI

INNER JOIN CUSTOMER CON SI.CustomerID= C.CustomerID

INNER JOIN SIDETAILS SIDON SI.SINumber= SID.SINumber

INNER JOIN ITEMS ION SID.ItemCode= I.ItemCode

Adi Cohn, I'll go through your post, thanks

also i have one question, is it possible to write a update query to a table with this RANK value

thanks for all

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

It is possible, but most of times I wouldn’t do it. I would only do it if I know that the data will not change and cause the ranking to modify. Here is a small demo that shows an update statement like that:

create table Grades (ID int not null identity(1,1), Grade int, Place int null)

go

--Inserting 10 records into the table

--(the number 10 at the end of the batch (go)

--caused it to run the batch 10 times

insert into grades (Grade)

select convert(int,(RAND()* 100) + 1)

go 10

--selecting the grades. Notice that place column is null

select * from Grades

--updateing the table

;with RankTheGrades as (

select id, Grade, RANK() over (order by grade desc) as Place

from Grades)

update Grades

set Grades.Place = RankTheGrades.place

from Grades inner join RankTheGrades on Grades.id = RankTheGrades.id

--Check if the update succeded

select * from Grades order by Grade desc

--clean up

drop table Grades

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

prakashdotc Ten Centuries Points: 1165 More actions · Answer 6

prakashdotc

Ten Centuries

Points: 1165

July 24, 2009 at 12:39 am

#1029488

Thanks Adi, i Got it Done.

Thanks for all