Rank This!!... - 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

General SQL Server Forums

New to SQL Server Programming

Rank This!!...

Author

Topic

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-14 : 08:11:00

Ok. Im not able to understand this logic please help. As you can see we have 2 columns of ranks, 1)normal 2)corrective. what is the logic behind this and how do u write a query for this? these ranks are for the Salary Column.
Imran,
"You truly do not know someone untill you fight them."-THE MATRIX.

EmpID Empname EmpSalary RankNormal RankCorrective
1 A 15000 1 1
2 B 10000 2 4
3 C 15000 1 1
4 D 4000 3 5
5 E 15000 1 1
6 F 1500 4 6
7 G 1500 4 6
8 H 500 5 8

HCLollo
Starting Member

49 Posts

Posted - 2005-10-14 : 08:17:07

Iktheone, seems to me that Corrective was added at some time after
inserting the Normal values, or to take into account the existence
of other ranks in the middle...something like...


NormalRank CorrectiveRank
1..........1
n/a........2
n/a........3
2..........4
3..........5
4..........6
n/a........7
5..........8

Does this make any sense?

HCL

"If it works fine, then it doesn't have enough features"

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-14 : 08:21:35

nope i guess not HCL. u see, there are three 15000's in the salary. in both normal and corrective, they would be ranked as '1,'1','1'. not as 'n/a'. i hope u understand. now tell me how the rest of the logic works?
Imran,
"Have no fear cause IK is here!"-Imran

HCLollo
Starting Member

49 Posts

Posted - 2005-10-14 : 08:30:31

I meant that when the data was entered, all that was to be used was
the NormalRank, and probably sometime after the opportunity for
creating new ranks sprang up, and so CorrectiveRank was added...
Yet, I've misspelled my example


NormalRank CorrectiveRank
1..........1
?..........2
?..........3
2..........4
3..........5
4..........6
?..........7
5..........8

Maybe so it's more readable? n/as stood for not available, since you
didn't provide value with 2,3 or 7 as CorrectiveRank...they could
match the value "before" them, or the one "after"...though probably
the most important columns right now becomes CorrectiveRank IMO...

HCL

"If it works fine, then it doesn't have enough features"

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 04:36:35

ok thats fine but, how do i write a query for this? with the ERROR CORRECTION for duplicate ranks?
Imran,
"Do think that is air your breathing?!"-Morpheus(MATRIX)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 04:49:03

Can you post your expected result?

Madhivanan

Failing to plan is Planning to fail

HCLollo
Starting Member

49 Posts

Posted - 2005-10-17 : 05:07:41

Welcome back Imran

Ok, so what do you want the query for?
Retrieve the salary and the corrective from the normal rank?
Something like:


SELECT 'RankCorrective'=
 CASE
  when NormalRank = 1 then 1
  when NormalRank = 2 then 4
  when NormalRank = 3 then 5
  when NormalRank = 4 then 6
  when NormalRank = 5 then 8
 END,'EmpSalary'=
 CASE
  when NormalRank = 1 then 15000
  when NormalRank = 2 then 10000
  when NormalRank = 3 then 4000
  when NormalRank = 4 then 1500
  when NormalRank = 5 then 500
 END
FROM yourTable

maybe is what you're looking for?

And exactly where/when do you expect duplicate ranks?

HCL

"If it works fine, then it doesn't have enough features"

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 06:32:36

well, mr.madhivanan, the expected result what i need it at the TOP of this forum.

and HCLollo, ur kind of close, but still its not clear enough. i want two different type of ranks, normal and corrective, in two different columns. preferably we can use an INDENTITY column to get sorta' a rownumber and rank the salary accordingly.

Imran,
"Its not how you play the game, its how the game plays you!"-HHH(WWE Wrestling).

HCLollo
Starting Member

49 Posts

Posted - 2005-10-17 : 06:43:01

Mmm...Imran, I'm getting lost now...


SELECT 'Don''t understand question'
FROM HCLollo JOIN madhivanan
ON query IS NOT the_one_wanted
WHERE iktheone IS NOT clearer

HCL

"If it works fine, then it doesn't have enough features"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 07:20:06

Do you want to do this using Query Analyser only?

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 07:28:43

well, mr.madhivanan u got it right. EVERYTHING in QUERY ANALYSER. because its just for practice.

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 07:32:53

delete from HCLollo where Doubt='true' and mind like '%confusion%'

(0) Row(s) Affected!

well HCL what i want is create an identity column and use it to rank the salary accordingly. i want only CORRECTIVE ranking. that will do.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-17 : 07:51:05

But Identity columns will have the unique ranks.. !!!!.. and then what about the salary with the similary amount like
1500
1500..
what rank they will be belong to .. 6,7 or 6,6???

Complicated things can be done by simple thinking

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-17 : 08:07:54

well chiragkhabaria, good question.
1500
1500
would belong to 6,6 because they are the same value so they get the same rank. if there are three 1500's then all three would get 6th rank. and the next rank would be 9 and not 7.

HCLollo
Starting Member

49 Posts

Posted - 2005-10-17 : 08:50:37

Ok Imran, I'm sorry: totally misjudged your needs

Time to try to solve this then...

HCL

"If it works fine, then it doesn't have enough features"

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-18 : 07:16:41

did u guys come up with any possibility? i feel we can use some coun(*) and group by. but dunno how exactly it works.

Imran,

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-10-20 : 02:55:53

Hi,
I Guess this will solve your Problem
select a.*, rank= (select count(*)+ 1 from TableName where ranknormal < a.ranknormal ) from TableName a order by ranknormal

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-20 : 04:40:54

create table rank
(
Empid int identity(1,1) constraint pk_idrank primary key clustered,
Empname varchar(15),
Empsalary numeric,
)

insert rank(Empname,Empsalary)values('A','15000')
insert rank(Empname,Empsalary)values('B','10000')
insert rank(Empname,Empsalary)values('C','15000')
insert rank(Empname,Empsalary)values('D','4000')
insert rank(Empname,Empsalary)values('E','15000')
insert rank(Empname,Empsalary)values('F','1500')
insert rank(Empname,Empsalary)values('G','1500')
insert rank(Empname,Empsalary)values('H','500')

Ok i made a mistake. the table doesnt have any two columns like Ranknormal or Rankcorrect. It just has the Empname and Empsalary. The query should just calculate the corrective rank and the normal rank based on the salary on the fly and display it. what do i do?

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-10-20 : 04:53:47

Hi,
U can do like this
select a.*,NormalRank = (select count(distinct Empsalary)+ 1 from RANK where Empsalary > a.Empsalary ) ,RANKCorrective= (select count(*)+ 1 from RANK where Empsalary > a.Empsalary ) from RANK a order by Empsalary desc

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-20 : 05:02:44

Thanks shallu! ur awesome! thats exactly what i wanted.

CASE CLOSED!

good job boys we're heading home!

THE END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 05:13:23

Note that if your table has thousands of data, running that query will take full SQL Server resource so that other applications connected to that Server may get TimeOut Error

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources