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
 General SQL Server Forums
 New to SQL Server Programming
 Building sql

Author  Topic 

gamaz2
Starting Member

31 Posts

Posted - 2014-04-14 : 17:37:37
Hi
I have a table as follows:
STUDENT STRM UNT_TAKEN
A 2127 3
A 2113 3
A 1923 6
A 1897 12
A 1893 3
B 1891 10
B 1887 16
B 1883 11

I need to extract unique row for each student A and B such that the maximum strm along with the units_taken shows up.
For example the output would be the following:

student strm units_taken
A 2127 3
B 1891 10

I would one create a sql to get the above result. Any direction is appreciated.

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-14 : 20:53:02
[code]SELECT
STUDENT student,
STRM strm,
UNT_TAKEN units_taken
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY STUDENT ORDER BY STRM DESC) ROWNUM,
*
FROM YourTable
) SOURCE
WHERE ROWNUM = 1
ORDER BY STUDENT[/code]OR
[code]SELECT
YourTable.STUDENT student,
YourTable.STRM strm,
YourTable.UNT_TAKEN units_taken
FROM YourTable
INNER JOIN (SELECT STUDENT, MAX(STRM) MAX_STRM FROM YourTable GROUP BY STUDENT) FILTER
ON YourTable.STUDENT = FILTER.Student
AND YourTable.STRM = FILTER.MAX_STRM
ORDER BY YourTable.STUDENT[/code]

-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

gamaz2
Starting Member

31 Posts

Posted - 2014-04-15 : 14:36:54
Thanks for your help Nagino. I appreciate it.
Go to Top of Page
   

- Advertisement -