select a row with a column have maximum value

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

SQL Server 2005 Forums

Transact-SQL (2005)

select a row with a column have maximum value

Author

Topic

younas02
Starting Member

28 Posts

Posted - 2012-06-26 : 11:56:22

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 - 2012-06-26 : 12:00:31

try this....

SELECT DISTINCT TESTNAME, MAX(MARKS)
FROM TABLENAME
WHERE NAME = 'ALI'
GROUP BY TESTNAME

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 12:04:46

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 12:06:56

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

younas02
Starting Member

28 Posts

Posted - 2012-06-26 : 12:23:29

i want to select all fields from table where different subjects have highest marks. i tried all you posted but not working

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 13:22:47

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources