CODEWITHSUNDEEP
sqloracle-databaseselectunique
Marcus Aurelius
Marcus Aurelius

Reputation:

SQL Select Entire Row by Distinct Columns

I need an sql statement which will allow me to select an entire row from oracle database, but by distinct columns. Here is a simplified database:

    Project_Num    Title        Category
    0              Project 1    Admin
    0              Project 1    Development
    1              Project 2    Admin
    2              Project 3    Development

I need my statement to return the following result set:

0     Project 1    Admin
1     Project 2    Admin
2     Project 3    Development

So each project is returned based on whether its project_num and title are unique. If a project has 2+ entries where its category is different, I need to only select that project once (it doesn't matter which entry I select for that project).

Can anyone help me please?

Upvotes: 1

Views: 7646

Answers (3)

davesbrain
davesbrain

Reputation: 606

SELECT *
FROM (SELECT DISTINCT YourDistinctField FROM YourTable) AS A
CROSS APPLY 
( SELECT TOP 1 * FROM YourTable B 
  WHERE B.YourDistinctField = A.YourDistinctField ) AS NewTableName

I have been trying to figure this out for hours now, I tried lots of solutions that didn't work and finally got it by searching for "joining top 1" and adapting that solution I found to a distinct search.

Upvotes: 2

Stephen Mesa
Stephen Mesa

Reputation: 4797

Do you even need to have the Category column in your result set?

If not then you could just use:

SELECT DISTINCT Project_Num, Title
FROM MyTable

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562330

SELECT Project_Num, Title, MIN(Category) AS Category
FROM MyTable
GROUP BY Project_Num, Title;

Upvotes: 6

Related Questions