SQL Combine two rows into a select statement

Question

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 18-Jun-13 5:50am

MJ_

Updated 18-Jun-13 7:22am

v3

Add a Solution

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

mgoad99 · Answer 1 · 2013-06-18T10:23:00

Solution 3

You could also try an aggregate (group by)

SQL

SELECT      offer_id_new
           ,max(Date1)
  FROM [PCGRecruitingDatabaseUpsize].[dbo].[ExtendOffer]
GROUP BY offer_id_new

Posted 18-Jun-13 10:23am

mgoad99

José Amílcar Casimiro · Answer 2 · 2013-06-18T06:13:00

Solution 1

Hello,

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

Cheers

Posted 18-Jun-13 6:13am

José Amílcar Casimiro

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.

MJ_ · Answer 3 · 2013-06-18T08:06:00

Solution 2

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.

Posted 18-Jun-13 8:06am

MJ_

Comments

José Amílcar Casimiro 18-Jun-13 17:45pm

wtf?