Select Distinct from a row select

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

General SQL Server Forums

New to SQL Server Programming

Select Distinct from a row select

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2014-05-25 : 16:09:10

Hi

Thanks to other posts in this forum, I've been able to select a specific row from a table:


SELECT classDisciplineID FROM
	(SELECT ROW_NUMBER()
		OVER (ORDER BY classDisciplineID) AS Row, *
	FROM @vt_MemberClassesCalendar) AS TMP_MemberClassesCalendar
WHERE Row = @NumberOfDisciplinesAttendedInTerm_RowCount

It works fine, but I need to use DISTINCT on the table called (@vt_MemberClassesCalendar) to filter the results correctly. I've tried adding 'DISTINCT classDisciplineID' to both SELECT calls, but it still returns the full table?

Any thoughts?

Thanks as always

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-05-26 : 01:58:43

Use Below Query:

SELECT classDisciplineID FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY aa.classDisciplineID) AS Row, *
FROM
(SELECT DISTINCT ColumnName1,ColumnnName2 .....
FROM @vt_MemberClassesCalendar) AS aa) AS TMP_MemberClassesCalendar
WHERE Row = @NumberOfDisciplinesAttendedInTerm_RowCount

Just mentions all column name from table @vt_MemberClassesCalendar but excluding your primary key column.

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2014-05-26 : 06:12:56

Fantastic! Many thanks

quote:
Originally posted by GouravSaxena1987

Use Below Query:

SELECT classDisciplineID FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY aa.classDisciplineID) AS Row, *
FROM
(SELECT DISTINCT ColumnName1,ColumnnName2 .....
FROM @vt_MemberClassesCalendar) AS aa) AS TMP_MemberClassesCalendar
WHERE Row = @NumberOfDisciplinesAttendedInTerm_RowCount

Just mentions all column name from table @vt_MemberClassesCalendar but excluding your primary key column.

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-05-26 : 12:31:38

Welcome :)

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources