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 · 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 IDStudentExam FROM table GROUP BY StudentExam ) SELECT ROW_NUMBER() OVER(ORDER BY ID) AS IDStudentExam 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 INTStudent 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 StudentExam FROM @Student ) SELECT Row_Number() OVER (ORDER BY StudentExam desc) AS IDStudentExam FROM cte ORDER BY StudentExam 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 IDStudentExam FROM table GROUP BY StudentExam ) SELECT ROW_NUMBER() OVER(ORDER BY ID) AS IDStudentExam FROM CTE Or: SELECT MIN(ID) AS IDStudentExam FROM table GROUP BY StudentExam ORDER BY ID ASC