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
 Building sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gamaz2
Starting Member

31 Posts

Posted - 04/14/2014 :  17:37:37  Show Profile  Reply with Quote
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

Japan
65 Posts

Posted - 04/14/2014 :  20:53:02  Show Profile  Reply with Quote
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
OR
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


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

gamaz2
Starting Member

31 Posts

Posted - 04/15/2014 :  14:36:54  Show Profile  Reply with Quote
Thanks for your help Nagino. I appreciate it.
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.05 seconds. Powered By: Snitz Forums 2000