Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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. [CODE]TABLE 1CRN TERM STUDENTID COURSE GRADE CREDITHRS SEQ CORRECTSEQ10290 201210 1340 F1101 B- 6 4 1 10290 201210 1340 F1101 C 4 2 1 10290 201210 1340 F1101 D+ 3 1 110290 201210 1340 F1101 D+ 4 3 1 10290 201210 1340 F1101 P 3 1 2 [/CODE]Therefore I would like the table to output looking like this:[CODE]TABLE 2CRN TERM STUDENTID COURSE GRADE CREDITHRS SEQ CORRECTSEQ10290 201210 1340 F1101 B- 6 4 1 10290 201210 1340 F1101 C 4 2 110290 201210 1340 F1101 D+ 4 3 1 10290 201210 1340 F1101 P 3 1 2 [/CODE]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
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-04-22 : 14:19:14
[code]select * from( select *, rank() over (partition by crn,term,studentid,course order by correctseq desc) as RN from YourTable) s where RN = 1;[/code]
velvet_tiger
Starting Member
12 Posts
Posted - 2013-04-22 : 15:08:15
Hi James,I tried this but it does not work. It only outputs those with a CORRECTSEQ of 1.
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-04-22 : 15:11:27
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;