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?
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.
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
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;
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.
TRY THIS SIMPLE QUERY TO GET RESULT
SELECT TOP 1 ID,NAME,mark FROM STUDENT
WHERE SUBJECT ='JAVA'
ORDER BY MARK DESC
