SQL Query- 1 student for distinct Exams.

Question

Hi all...
I need help in forming a SQL Query.
I have the following table -
ID
Student
Exam
1
A
MAT
2
A
MAT
3
A
CAT
4
B
CAT
5
B
CAT
6
C
MAT
7
C
MAT
8
C
CAT
Now, I want the following output as result -
ID
Student
Exam
1
A
MAT
2
A
CAT
3
B
CAT
4
C
MAT
5
C
CAT
1 Student have appeared for distinct 2 exams - MAT and CAT.
How do I do this??
PLease help.

Vulpes · Accepted Answer

Try also this:
SELECT ROW_NUMBER() OVER(ORDER BY sq.Student) AS ID, sq.Student, sq.Exam FROM (SELECT DISTINCT Student, Exam FROM table ORDER BY Student) AS sq

Vulpes · Answer

Try:
SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY ID) AS 'ID', Student, Exam FROM table

Riddhi Valecha · Answer

Thanks a lot...
That query worked...
I got the output as I wanted...
Thanks a ton again to all for helping.

Ken H · Answer

I   think   it   has  to  do   this :
;WITH CTE AS
(
SELECT  MIN(ID) AS ID,Student,Exam FROM table GROUP BY Student,Exam
)
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ID,Student,Exam FROM CTE
In addition: this 'distinct' only a single column filter to be useful.If more than one column it is ineffective. So,'GROUP BY' clause is necessary to achieve this.

Khan Abrar Ahmed · Answer

Hi try this
DECLARE @Student AS TABLE (ID INT,Student VARCHAR(64),Exam VARCHAR(64))
INSERT INTO @Student VALUES
(1,'A','MAT'),(2,'A','MAT'),(3,'A','CAT'),(4,'B','CAT'),(5,'B','CAT'),(6,'C','MAT'),(7,'C','MAT'),(8,'C','CAT');
WITH cte AS (
SELECT distinct Student,Exam FROM  @Student
)
SELECT Row_Number() OVER (ORDER BY Student,Exam desc) AS ID,Student,Exam FROM cte ORDER BY Student,Exam desc

Riddhi Valecha · Answer

Hi all..
Thanks for these queries.
But, I am not getting the output.
The exam names are appearing twice.
Its like, student A have appeared for MAT - 2 times and CAT - 1 time.
So, in the output, I want only 2 records for Student - A; i.e. -
1. A Mat
2. A Cat.
-----
Please help.

Ken H · Answer

Please try:
;WITH CTE AS   (    SELECT  MIN(ID) AS ID,Student,Exam FROM table GROUP BY Student,Exam  )  SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ID,Student,Exam FROM CTE
Or:
SELECT  MIN(ID) AS ID,Student,Exam FROM  table  GROUP BY Student,Exam ORDER BY ID ASC