June 26, 2015 at 4:05 am
Hi Sir,
I would like to rank my records as I mentioned below example but without distrubing the order_key and order_key_rank
After executing the query I am getting the result below,
ddl script,
CREATE TABLE abc_order
(id int,name nvarchar(15),order_key int,order_key_rank int)
insert into script,
INSERT INTO abc_order VALUES (10,'KING',1,1)
INSERT INTO abc_order VALUES (20,'ABC',1,2)
INSERT INTO abc_order VALUES (30,'BBC',1,3)
INSERT INTO abc_order VALUES (40,'KING',1,4)
INSERT INTO abc_order VALUES (50,'TTT',2,1)
INSERT INTO abc_order VALUES (50,'TTT',2,2)
INSERT INTO abc_order VALUES (50,'TTT',2,3)
INSERT INTO abc_order VALUES (60,'RRR',2,4)
INSERT INTO abc_order VALUES (70,'UUU',2,5)
sql code,
SELECT ROW_NUMBER() OVER (
ORDER BY order_key
,order_key_rank
) AS rnk
,id
,NAME
,order_key
,order_key_rank
FROM abc_order
ORDER BY order_key
,order_key_rank
After executing the query I am getting the result below,
rnkidnameorder_keyorder_key_rank
110KING11
220ABC12
330BBC13
440KING14
550TTT21
650TTT22
750TTT23
860RRR24
970UUU25
but I would like to get the output like below
rnkidnameorder_keyorder_key_rank
110KING11
220ABC12
330BBC13
440KING14
550TTT21
550TTT22
550TTT23
660RRR24
770UUU25
So request you please help me how to resolve this.
Regads,
Kiran
June 26, 2015 at 4:40 am
Try the below code
SELECT DENSE_RANK() OVER (ORDER BY id,NAME) AS rnk
,id
,NAME
,order_key
,order_key_rank
FROM abc_order
ORDER BY order_key
,order_key_rank
June 26, 2015 at 7:34 am
Thanks Sir,
its working fine for few columns but I do have more then 5 columns in my sql and its not giving the proper ranking using DENSE_RANK function so what was the problem Sir?
June 26, 2015 at 8:39 am
kiran.rajenimbalkar (6/26/2015)
Thanks Sir,its working fine for few columns but I do have more then 5 columns in my sql and its not giving the proper ranking using DENSE_RANK function so what was the problem Sir?
Can you post some sample data with the additional columns so that I can look into it. Ranking doesn't differ with the number of columns in the SELECT rather changes with the OVER clause.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply