CODEWITHSUNDEEP
sqlsql-serverdatabaseselectsql-order-by
Ren Xenon
Ren Xenon

Reputation: 11

find maximum with specific data in sql server

I still confused about sql server, for example i have student table and i try to find maximum mark for java student 

STUDENT
| id | name  | mark | subject |
|  1 | jenny |  67  |  db    | 
|  2 | mark  |  74  |  java  |
|  3 | nala  |  90  |  java  |

i try to get output like this

|  3 | nala  |  90  |

i write this in sql, but the output is empty.

SELECT id,name,mark
FROM student
WHERE subject='Java'
 AND mark=
(SELECT max(mark) FROM student);

how i'm supposed to correct it?

Upvotes: 1

Views: 78

Answers (5)

DBname
DBname

Reputation: 11

TRY THIS SIMPLE QUERY TO GET RESULT

SELECT TOP 1 ID,NAME,mark FROM STUDENT
  WHERE SUBJECT ='JAVA'
  ORDER BY MARK DESC

Upvotes: 0

SMA
SMA

Reputation: 37033

With what you are trying (with subquery), you could do:

SELECT id,name,mark
FROM student
WHERE subject='Java' 
AND   mark = (SELECT MAX(mark) 
              FROM student 
              WHERE subject='Java');

You are trying to fetch max of all the records irrespective of subject name.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Use TOP 1 with ORDER BY clause to fetch highest data

Try this: 

SELECT TOP 1 id, name, mark
FROM student
WHERE subject = 'Java'
ORDER BY mark DESC;

OR

SELECT id, name, mark
FROM (SELECT id, name, mark, ROW_NUMBER() OVER (ORDER BY mark DESC) AS RowNum
      FROM student
      WHERE subject = 'Java'
     ) AS A 
WHERE RowNum = 1;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93734

You don't need to use sub query. Use Top 1 with ties to get the student with max marks and also if the max mark is shared by more than one student

Where condition will filter the result to have only subject = 'Java' after that Top 1 with order by will fetch you the max mark in java

SELECT TOP 1 with ties id, name, mark
FROM student
WHERE subject = 'Java'
ORDER BY mark DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

There are many ways to get what you want in SQL. However, you should understand the problem with your approach:

SELECT id, name, mark
FROM student
WHERE subject = 'Java' AND
      mark = (SELECT max(mark) FROM student);

The problem is that the maximum value of mark may not be for 'Java'. Hence, no rows can pass both where conditions.

You need to repeat the filter in the subquery, either explicitly:

SELECT id, name, mark
FROM student
WHERE subject = 'Java' AND
      mark = (SELECT max(mark) FROM student WHERE subject = 'Java');

Or using a correlated subquery:

SELECT s.id, s.name, s.mark
FROM student s
WHERE s.subject = 'Java' AND
      s.mark = (SELECT max(mark) FROM student s2 WHERE s2.subject = s.subject);

Notice that the last query uses table aliases. You should learn to use these in your queries; sometimes they are necessary and they generally make queries easier to write, read, and understand.

Upvotes: 2

Related Questions