SQL: How to select only distinct rows based on some property values

Question

I have an sql-view in my database which uses a UNION to concat different results (one side contains a left join whereas the other side uses NULL as placeholder for the missing values). In one part of the join one of the properties (a foreign key) is null whereas in the other one it contains a value (for the same unique ID). So my join will give me a result set like this:

ID     |    ForeignKey
1           NULL
1           123
2           NULL
3           NULL
4           NULL
4           234

I try to reduce this result set to contain only entries with a unique ID, but I cannot check the ForeignKey against NULL because then I would filter out all other rows where the key is NULL.

What I am looking for is a way to tell SQL to check for the entire result set whether the same ID is used twice. If it finds such a duplicate (as the entries with ID 1 and 4 above) it should only display the one with the value (123 and 234). The entry with NULL should be filtered away.

If no duplicate is found (as for ID 2 and 3) that entry should be used.

So I want to transform the above result set to this:

ID     |    ForeignKey
1           123
2           NULL
3           NULL
4           234

I tried to solve the problem by using the OVER keyword and PARTITION By ID where I set the RowNumber to be equal to 1. This filters away the duplicate entries however it chooses always the entry with NULL instead of the one with the actual key, so the WRONG result looks like this:

ID     |    ForeignKey
1           NULL
2           NULL
3           NULL
4           NULL

Your solution with OVER(PARTITION BY) would have worked if you had also included an ORDER BY ForeignKey DESC — Tab Alleman
– Tab Alleman, Commented Nov 14, 2014 at 12:55

M.Ali · Accepted Answer · 2014-11-14 12:54:09Z

4

SELECT ID , MAX(ForeignKey) AS ForeignKey
FROM TableName
GROUP BY ID

answered Nov 14, 2014 at 12:54

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philipp Over a year ago

Thank you! It works! The only problem with this is that I now have to specify every column manually. Otherwise there is an error that column xyz cannot be selected because it is not in the aggregate ofr Group By Clause...

Collectives™ on Stack Overflow

SQL: How to select only distinct rows based on some property values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related