December 1, 2016 at 5:14 pm
below query returns expected result set. but i am thinking if i have different student status it many take lead in row number based on sorting order.
fulltime will always take lead for a student even though they enrolled multiple ways.
drop table #Student
create table #Student
(
StudentId int not null,
Studentstatus char(30),
FeePaid varchar(10)
)
insert into #Student
select 1 , 'Intern', '$1000'
union select 1 , 'Full time', '$2000'
union select 2 , 'Full time', '$5000'
drop table #tbl2
create table #tbl2
(
StudentId int not null,
StudentNm varchar(30),
)
insert into #tbl2
select 1 ,'John'
union select 2 ,'Rayn'
;WITH summary AS (
SELECT p.Studentstatus,
p.StudentId ,
p.FeePaid,
ROW_NUMBER() OVER(PARTITION BY p.Studentstatus
ORDER BY p.StudentId DESC) AS rownum
FROM #Student p
join #tbl2 b on p.StudentId =b.StudentId)
SELECT *
FROM summary s
WHERE s.rownum = 1
December 1, 2016 at 8:14 pm
Just add the necessary ORDER BY with a CASE statement that forces 'Full Time' to be first... like this...
WITH summary AS
(
SELECT p.Studentstatus
,p.StudentId
,p.FeePaid
,RowNum = ROW_NUMBER() OVER(PARTITION BY p.Studentstatus
ORDER BY p.StudentId DESC)
FROM #Student p
JOIN #tbl2 b ON p.StudentId = b.StudentId
)
SELECT *
FROM summary s
WHERE s.rownum = 1
ORDER BY CASE WHEN Studentstatus = 'Full time' THEN 0 ELSE 1 END
,Studentstatus
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2016 at 8:21 pm
thanks Jeff for your reply it worked.
i try to do other way with case statement and group by without success 😉
lets see if any one come up with different way of doing it.
December 2, 2016 at 8:24 am
Something seems off here. You're worried about a new student status affecting the sort order, but that's impossible, because the student status defines the partition. You say that you want full time to take lead for a student, but you aren't looking at groups based on students, you're looking at groups based on status.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2016 at 8:31 am
mxy (12/1/2016)
;WITH summary AS (
SELECT p.Studentstatus,
p.StudentId ,
p.FeePaid,
ROW_NUMBER() OVER(PARTITION BY p.Studentstatus
ORDER BY p.StudentId DESC) AS rownum
FROM #Student p
join #tbl2 b on p.StudentId =b.StudentId)
SELECT *
FROM summary s
WHERE s.rownum = 1
I agree with Drew. Something is not right with your query.
As written, you'll receive only one student per status, and it will be the student with the Highest StudentID. You said the query returns the expected results, but your question implies you want different results.
mxy (12/1/2016)
below query returns expected result set. but i am thinking if i have different student status it many take lead in row number based on sorting order.fulltime will always take lead for a student even though they enrolled multiple ways.
Your sample data does have StudentID 1 with two statuses, Intern & Full time, and the query returns status Intern.
I'm not sure exactly what you're needing, but based on your question, use Jeff's suggestion in your Row Number expression :
WITH summary AS
( SELECT p.Studentstatus,
p.StudentId,
p.FeePaid,
--For Each Student, give me the full time status record, otherwise give me the next status alphabetically for that student
rownum = ROW_NUMBER() OVER (PARTITION BY p.StudentId ORDER BY CASE WHEN p.Studentstatus = 'Full time' THEN 0 ELSE 1 END,p.Studentstatus)
FROM #Student AS p
On a separate note, as written, there is no reason for your join to #tbl2 and it is adding overhead to your Row Number partitioning. If it is just to filter #student, you might improve performance by doing an Exists check instead.
WHERE EXISTS (SELECT 1 FROM #tbl2 AS t WHERE t.StudentId = p.StudentId)
)
SELECT * FROM summary AS s WHERE s.rownum = 1;
Wes
(A solid design is always preferable to a creative workaround)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply