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.
Author |
Topic |
gamaz2
Starting Member
31 Posts |
Posted - 2014-04-14 : 17:37:37
|
Hi I have a table as follows:STUDENT STRM UNT_TAKENA 2127 3A 2113 3A 1923 6A 1897 12A 1893 3B 1891 10B 1887 16B 1883 11I 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_takenA 2127 3B 1891 10I 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_takenFROM ( SELECT ROW_NUMBER() OVER(PARTITION BY STUDENT ORDER BY STRM DESC) ROWNUM, * FROM YourTable ) SOURCEWHERE ROWNUM = 1ORDER BY STUDENT[/code]OR[code]SELECT YourTable.STUDENT student, YourTable.STRM strm, YourTable.UNT_TAKEN units_takenFROM YourTableINNER JOIN (SELECT STUDENT, MAX(STRM) MAX_STRM FROM YourTable GROUP BY STUDENT) FILTER ON YourTable.STUDENT = FILTER.Student AND YourTable.STRM = FILTER.MAX_STRMORDER BY YourTable.STUDENT[/code]-------------------------------------From JapanSorry, my English ability is limited. |
|
|
gamaz2
Starting Member
31 Posts |
Posted - 2014-04-15 : 14:36:54
|
Thanks for your help Nagino. I appreciate it. |
|
|
|
|
|