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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 select a row with a column have maximum value		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

younas02
Starting Member

Pakistan
21 Posts
Posted - 06/26/2012 :  11:56:22  Show Profile  Reply with Quote
i have a table with 4 fields, name, testname,marks,status. testname can b of different names. for exaple testname can b word, excel, power point. and thhese testname may b have repeated values i.e there can b 2 testname with word 2 with excel and so on.
there can b many records in table. the task is to select rows having maximum value in marks field.

i used select testname, max(marks) from tablename where name is='ali' group by testname,
it give correct results u can say if we have six record of three testname with 2 of each testname, it gives 3 records of maximum marks.
but when i want to select more than one column it gives all results of ali.
can any one help me????????

DCW87
Starting Member

6 Posts
Posted - 06/26/2012 :  12:00:31  Show Profile  Reply with Quote
try this....

SELECT DISTINCT TESTNAME, MAX(MARKS)
FROM TABLENAME
WHERE NAME = 'ALI'
GROUP BY TESTNAME
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 06/26/2012 :  12:04:46  Show Profile  Reply with Quote
You can use the select with the aggregate you already created and join to it. For example:
SELECT
	A.name, 
	A.testname,
	A.marks,
	A.status
FROM
	Table AS A
INNER JOIN
	(
		SELECT 
			TestName,
			MAX(Marks) AS MaxMarks
		FROM
			Table
		WHERE
			name = 'ali'
		GROUP BY
			TestName	
	) AS B
	ON A.testname = B.testname
	AND A.marks = B.MaxMarks
EDIT: Forgot aliases.
Edited by - Lamprey on 06/26/2012 12:10:18
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 06/26/2012 :  12:06:56  Show Profile  Reply with Quote
Another approach is to you a ranking function like:
SELECT
	name, 
	testname,
	marks,
	status
FROM
	(		
		SELECT 
			name, 
			testname,
			marks,
			status,
			ROW_NUMBER() OVER (PARTITION BY testname ORDER BY marks DESC) as RowNum
		FROM
			Table
		WHERE
			name = 'ali'
	) AS A
WHERE
	RowNum = 1
Go to Top of Page

younas02
Starting Member

Pakistan
21 Posts
Posted - 06/26/2012 :  12:23:29  Show Profile  Reply with Quote
i want to select all fields from table where different subjects have highest marks. i tried all you posted but not working
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 06/26/2012 :  13:22:47  Show Profile  Reply with Quote
I don't see any reference to Subject in your original post. How about you start over and supply DDL, DML and expected output so we can help you better. Here are some links that can help you prepare that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/
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