Click here to Skip to main content
15,946,179 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have two columns that I am running a distinct select statement for. The problem is I really want just a distinct for the first column, the second one I would like to take the one that isn't NULL or if both are NULL then pass NULL.

I am using this select statement.

  FROM [PCGRecruitingDatabaseUpsize].[dbo].[ExtendOffer] 

And it returns me this

offer_id_new    Date 
819    2009-05-08 00:00:00.000
864    NULL
864    2010-08-08 00:00:00.000
866    NULL

For the two ID's of "864", I would like to pull only one per offer_id_new but this ID "864" has both a NULL date and a date. If this exists I would like to just have 1 row which is the date.
Updated 18-Jun-13 7:22am
Richard C Bishop 18-Jun-13 11:52am    
So add a clause to your WHERE that only allows dates that are not NULL just like you did with the id column.
MJ_ 18-Jun-13 13:09pm    
But NULL is not the problem. I want it to pull them when they are NULL. The problem is when there are two rows, one being NULL and the other not being NULL for date. I would only want the one that is not NULL

You could also try an aggregate (group by)

SELECT      offer_id_new
  FROM [PCGRecruitingDatabaseUpsize].[dbo].[ExtendOffer]
GROUP BY offer_id_new
Share this answer

Alter the 'left join' into a 'inner join'.

Share this answer
MJ_ 18-Jun-13 13:20pm    
The Date isn't NULL because it is part of a join. The Date is actually NULL in the database sometimes.

I have modified the select statement so it is less confusing and only deals with the issue, sorry.
I figured it out. Insert into a temp table where Date is not null. Then insert again if the id does not exist in the temp table.
Share this answer
José Amílcar Casimiro 18-Jun-13 17:45pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900