need help row_number() over( partition

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 2008 Forums

Transact-SQL (2008)

need help row_number() over( partition

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-24 : 00:43:16

I've table and data as following,


declare @tKursus table (kursusIdx int, calon varchar(50), 
pilihanApplicant int, totalMerit decimal(10,5));

insert into @tKursus values('184','NURUL AMIRA BINTI ABDULLAH',-2147483079,32.66667);
insert into @tKursus values('184','NURUL SYAFIKAH',-2147483179,42.66667);
insert into @tKursus values('184','JOHN CENA',-2147483279,30.66667);
insert into @tKursus values('172','MOHAMMAD ZAEIM BIN MOHAMMOOD',-2147483073,33.11111);
insert into @tKursus values('172','NURUL SYAFIKAH',-2144483179,42.66667);
insert into @tKursus values('172','MOHAMMAD KHAIRUL',-2147383073,44.11111);
insert into @tKursus values('145','NURUL FARHANA ALI',-2144489179,47.66667);
insert into @tKursus values('145','JOHN CENA',-2123630733,30.66667);
insert into @tKursus values('145','MOHAMMAD ZAEIM BIN MOHAMMOOD',-2147455573,33.11111);
insert into @tKursus values('145','NURUL AMIRA BINTI ABDULLAH',-1000000900,32.66667);
insert into @tKursus values('136','JOHN CENA',-1000000009,30.66667);
/*
1. subjectIdx, and calon  is a unique
2. pilihanApplicant is unique
*/

To built mySelection, I've query as following


select kursusIdx, calon,pilihanApplicant, totalMerit,
row_number() over(partition by calon order by pilihanApplicant ASC) as mySelection
from @tKursus
order by kursusIdx desc

Now I want to built myRanking. My Expected result as following,

kursusIdx   | calon                        | pilihanApplicant     | totalMerit      | mySelection | myRanking
--------------------------------------------------------------------------------------------------------------------
184	          JOHN CENA	                     -2147483279	          30.66667	        1           3   /*Based on mySelection=1 and totalMerit, JOHN CENA is ranked to no 3*/
184	          NURUL AMIRA BINTI ABDULLAH	 -2147483079	          32.66667	        1           2   /*Based on mySelection=1 and totalMerit, NURUL AMIRA is ranked to no 2*/
184	          NURUL SYAFIKAH	             -2147483179	          42.66667	        1           1   /*Based on mySelection=1 and totalMerit, NURUL SYAFIKAH is ranked to no 1*/
172	          NURUL SYAFIKAH	             -2144483179	          42.66667	        2           3   /*Based on mySelection=2 and totalMerit, NURUL SYAFIKAH is ranked to no 3. Although NURUL SYAFIKAH have the higher totalMerit compare than MOHAMMAD ZAEIM, but NURUL SYAFIKAH's mySelection=2. MOHAMMAD ZAEIM's mySelection=1*/
172	          MOHAMMAD KHAIRUL	             -2147383073	          44.11111	        1           1   /*Based on mySelection=1 and totalMerit, MOHAMMAD KHAIRUL is ranked to no 1*/
172	          MOHAMMAD ZAEIM BIN MOHAMMOOD	 -2147483073	          33.11111	        1           2   /*Based on mySelection=1 and totalMerit, MOHAMMAD ZAEIM is ranked to no 2. Although MOHAMMAD ZAEIM have the lower totalMerit compare than NURUL SYAFIKAH, but NURUL SYAFIKAH's mySelection is 2. MOHAMMAD ZAEIM's mySelection=1*/
145	          MOHAMMAD ZAEIM BIN MOHAMMOOD	 -2147455573	          33.11111	        2           2
145	          JOHN CENA	                     -2123630733	          30.66667	        2           4
145	          NURUL AMIRA BINTI ABDULLAH	 -1000000900	          32.66667	        2           3
145	          NURUL FARHANA ALI	             -2144489179	          47.66667	        1           1
136	          JOHN CENA	                     -1000000009	          30.66667	        3           1

Please help me to built the myRanking query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 01:09:31

[code]
select *,row_number() over (partition by kursusIdx order by myselection,totalMerit desc) as myRanking
from
(
select kursusIdx, calon,pilihanApplicant, totalMerit,
row_number() over(partition by calon order by pilihanApplicant ASC) as mySelection
from @tKursus
)t
order by kursusIdx desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-24 : 01:09:53

declare @tKursus table (kursusIdx int, calon varchar(50), pilihanApplicant int, totalMerit decimal(10,5));

insert into @tKursus values('184','NURUL AMIRA BINTI ABDULLAH',-2147483079,32.66667);
insert into @tKursus values('184','NURUL SYAFIKAH',-2147483179,42.66667);
insert into @tKursus values('184','JOHN CENA',-2147483279,30.66667);
insert into @tKursus values('172','MOHAMMAD ZAEIM BIN MOHAMMOOD',-2147483073,33.11111);
insert into @tKursus values('172','NURUL SYAFIKAH',-2144483179,42.66667);
insert into @tKursus values('172','MOHAMMAD KHAIRUL',-2147383073,44.11111);
insert into @tKursus values('145','NURUL FARHANA ALI',-2144489179,47.66667);
insert into @tKursus values('145','JOHN CENA',-2123630733,30.66667);
insert into @tKursus values('145','MOHAMMAD ZAEIM BIN MOHAMMOOD',-2147455573,33.11111);
insert into @tKursus values('145','NURUL AMIRA BINTI ABDULLAH',-1000000900,32.66667);
insert into @tKursus values('136','JOHN CENA',-1000000009,30.66667);

select kursusIdx, calon,pilihanApplicant, totalMerit,
row_number() over(partition by calon order by pilihanApplicant ASC) as mySelection
into #Temp1 from @tKursus
order by kursusIdx desc

select *, ROW_NUMBER() OVER(partition by kursusIdx order by mySelection , totalMerit desc ) as myRanking from #Temp1
order by kursusIdx desc, pilihanApplicant

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-24 : 01:32:45

tq. both of you are great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:40:51

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources