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:
Hello,

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.

SQL
SELECT DISTINCT
           offer_id_new
           ,Date1
  FROM [PCGRecruitingDatabaseUpsize].[dbo].[ExtendOffer] 


And it returns me this

CSS
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.
Posted
Updated 18-Jun-13 7:22am
v3
Comments
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)

SQL
SELECT      offer_id_new
           ,max(Date1)
  FROM [PCGRecruitingDatabaseUpsize].[dbo].[ExtendOffer]
GROUP BY offer_id_new
 
Share this answer
 
Hello,

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

Cheers
 
Share this answer
 
Comments
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
 
Comments
José Amílcar Casimiro 18-Jun-13 17:45pm    
wtf?

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