Select Distinct and Return Multiple Columns

How does one select a distinct column value and still bring back all other associated columns for the distinct value?

In this example we have repeated [Requisition_Number]s and wish to find distinct values, but we still want the other columns returned also.

This ignores the DISTINCT and returns all the rows:

view plain print about
1SELECT [ID]
2        ,DISTINCT [Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM [Requisitions]
and is NOT what we want.

This returns JUST the distinct values for [Requisition_Number] and columns associated with those values.

view plain print about
1SELECT     [ID]
2        ,[Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM (
13SELECT     [ID]
14        ,[Requisition_Number]
15        ,[Status]
16        ,Title]
17        ,[Code]
18        ,[UID]
19        ,[Full_Name]
20        ,[City]
21        ,[State]
22        ,[Requisition_Justification]
23        ,[Date_Requisition_Opened]
24        ,ROW_NUMBER() OVER(PARTITION BY [Requisition_Number] ORDER BY [ID]) RN
25    FROM [Requisitions]) R
26    WHERE RN = 1

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner