SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding the Maximum of a row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

velvet_tiger
Starting Member

12 Posts

Posted - 04/22/2013 :  14:02:11  Show Profile  Reply with Quote
Hi y'all,

I have created a query that pulls the below table.This table shows students' grade. I would like to output the row with the max(correctseq). The correctseq table represents the most recent grade that was entered.



TABLE 1

CRN   TERM   STUDENTID COURSE  GRADE CREDITHRS SEQ CORRECTSEQ
10290 201210 1340      F1101	B-	6	4	1	
10290 201210 1340      F1101	C	4	2	1	
10290 201210 1340      F1101	D+	3	1	1
10290 201210 1340      F1101	D+	4	3	1	
10290 201210 1340      F1101	P	3	1	2	



Therefore I would like the table to output looking like this:



TABLE 2

CRN   TERM   STUDENTID COURSE  GRADE CREDITHRS SEQ CORRECTSEQ
10290 201210 1340      F1101	B-	6	4	1	
10290 201210 1340      F1101	C	4	2	1
10290 201210 1340      F1101	D+	4	3	1	
10290 201210 1340      F1101	P	3	1	2	



When I tried doing a max(CORRECTSEQ) it doesn't work. Below is my code that I have thus far. Any help would be greatly appreciated.

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 04/22/2013 :  14:19:14  Show Profile  Reply with Quote
select * from
( select *, rank() over (partition by crn,term,studentid,course order by correctseq desc) as RN
  from YourTable
) s where RN = 1;
Go to Top of Page

velvet_tiger
Starting Member

12 Posts

Posted - 04/22/2013 :  15:08:15  Show Profile  Reply with Quote
Hi James,

I tried this but it does not work. It only outputs those with a CORRECTSEQ of 1.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 04/22/2013 :  15:11:27  Show Profile  Reply with Quote
Sorry about that; see below
select * from
( select *, rank() over (partition by crn,term,studentid,course,seq order by correctseq desc) as RN
  from YourTable
) s where RN = 1;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000