Get row number result in correct order?

Question

I have a query to get JobRanking based on yearleftpost to get latest job followed by previous jobs worked.

select
    c.id,
    c.Fname,
    cfe.JobYear,
    cfe.YearLeftPost,
    cfe.Iscurrentjob,                                         
    ROW_NUMBER() OVER (Partition by c.id ORDER BY Iscurrentjob DESC, yearleftpost DESC) AS JobRanking
from
candidates c
left join Candidate_FunctionalExperience cfe on c.id = cfe.CandidateID

It gives me the result:

id  Fname    JobYear  YearLeftPost  Iscurrentjob JobRanking
EC2 sdsadsa  1430     1433              1           1
EC2 sdsadsa  1429     1430              0           2
EC2 sdsadsa  1424     1428              0           3
EC2 sdsadsa  1428     1428              0           4
EC2 sdsadsa  1424     1426              0           5

Now, I have a IscurrentJob (Boolean value) 0 or 1, I am not getting the correct order of JobRanking.

Edit the question add full sample output, as your `SELECT` statement has `6` columns but you display only `4` columns. — Yogesh Sharma, Jan 22 '20 at 17:13
Reasons not to use images are [here](http://meta.stackoverflow.com/a/285557/92546). — HABO, Jan 22 '20 at 17:21

score 1 · Accepted Answer · answered Jan 22 '20 at 17:13

1

If you want Iscurrenjjob=1 rows to be first in the rankings then add that to your ordering clause:

Partition by c.id ORDER BY Iscurrentjob DESC, yearleftpost DESC

answered Jan 22 '20 at 17:13

D Stanley

149,601
11
178
240

This gives me 2 JobRanking = 1 in the same result set. – RKKK Jan 22 '20 at 17:16
@RKKK for the same id? You should have distinct ranks for each id based on the partition. – D Stanley Jan 22 '20 at 17:18
Thanks Stanley :) ,This works fine now I tried with both scenarios when there is no current job and with a current job value, – RKKK Jan 22 '20 at 17:25
select c.id, c.Fname, cfe.JobYear, cfe.YearLeftPost, cfe.Iscurrentjob, ROW_NUMBER() OVER (Partition by c.id ORDER BY Iscurrentjob DESC, yearleftpost DESC) AS JobRanking from candidates c left join Candidate_FunctionalExperience cfe on c.id = cfe.CandidateID – RKKK Jan 22 '20 at 17:25
This works but not in the scenario when the yearleftpost is same. This is possible when a job is only for few months but we don't have any month/day field for calculation – RKKK Jan 22 '20 at 17:44

VBoka · Answer 2 · 2020-01-22T17:55:36.550

0

This should do the job.

 select
    c.id,
    c.Fname,
    cfe.JobYear,
    cfe.YearLeftPost,
    cfe.Iscurrentjob,                                         
    ROW_NUMBER() OVER (order by c.id) AS JobRanking
 from
 candidates c
 left join Candidate_FunctionalExperience cfe on c.id = cfe.CandidateID
 ORDER BY cfe.Iscurrentjob desc, cfe.YearLeftPost desc

edited Jan 22 '20 at 17:55

answered Jan 22 '20 at 17:24

VBoka

8,995
3
16
24

Edited the question but the ranking is still wrong when the date of Yearleftpost are same – RKKK Jan 22 '20 at 17:42
Hi @RKKK please show us how do you want it to be ? Thanks! – VBoka Jan 22 '20 at 17:49
I have updated my answer ... but on blind, without info I asked. Please try it and comment what you would like for order by to be ? Thanks! – VBoka Jan 22 '20 at 17:56

score 0 · Answer 3 · answered Jan 22 '20 at 17:25

0

With conditional sorting:

ROW_NUMBER() OVER (
  Partition by c.id 
  ORDER BY CASE WHEN cfe.Iscurrentjob = 1 THEN 0 ELSE 1 END, yearleftpost DESC
) AS JobRanking

answered Jan 22 '20 at 17:25

forpas

160,666
10
38
76

Edited the question but the ranking is still wrong when the date of Yearleftpost are same – RKKK Jan 22 '20 at 17:42
Post sample data and expected results for each scenario to clarify. – forpas Jan 22 '20 at 17:52

Get row number result in correct order?

3 Answers3