NonNull Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

NonNull Query

Author

Topic

pbeesley
Starting Member

4 Posts

Posted - 2008-08-21 : 14:54:03

create_date, col1, col2, col3, col4,
8/21/2008, red, <NULL>, <NULL>, <NULL>,
8/20/2008, <NULL>, brown, <NULL>, <NULL>,
8/19/2008, <NULL>, <NULL>, green, <NULL>,
8/18/2008, <NULL>, Green, <NULL>, <NULL>,
8/17/2008, Blue, <NULL>, brown, blue,

What SQL function could you use to return the most recent non-null values across these four columns? (red, brown, green, blue)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-21 : 15:22:27

select max(coalesce(col1,col2,col3,col4)),max(create_date)
from your table

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-21 : 15:49:21

What is your desired output? Sodeep's query will get you the max date and max "color", but not necessarily for the same day.

Maybe something like this?

DECLARE @Yak TABLE (create_date DATETIME, col1 VARCHAR(10), col2 VARCHAR(10), col3 VARCHAR(10), col4 VARCHAR(10))

INSERT @Yak
SELECT '8/21/2008', 'red', NULL, NULL, NULL
UNION ALL SELECT '8/20/2008', NULL, 'brown', NULL, NULL
UNION ALL SELECT '8/19/2008', NULL, NULL, 'green', NULL
UNION ALL SELECT '8/18/2008', NULL, 'Green', NULL, NULL
UNION ALL SELECT '8/17/2008', 'Blue', NULL, 'brown', 'blue'

SELECT coalesce(col1,col2,col3,col4)
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak)

pbeesley
Starting Member

4 Posts

Posted - 2008-08-21 : 15:51:05

SooDeep, Thanks for the advice, I think the solution involves a COALESCE too. I tried your query but it returns only two columns
red 2008-08-21

I'd like to see the most recent value of each of the four columns
red, brown, green, blue

pbeesley
Starting Member

4 Posts

Posted - 2008-08-21 : 16:07:55

Lamprey, your solution results in one column, "red"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-21 : 16:16:55

Then just use:

select coalesce(col1,col2,col3,col4)
from your table

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-21 : 16:32:41

quote:
Originally posted by pbeesley

Lamprey, your solution results in one column, "red"

Yes, that is why I asked what output you wanted.

Since you still havn't told us what output you want, all I can do is supply you with queries that meet your initial requirement: "return the most recent non-null values across these four columns? (red, brown, green, blue)"

SELECT col1
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col1 IS NOT NULL)

SELECT col2
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col2 IS NOT NULL)

SELECT col3
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col3 IS NOT NULL)

SELECT col4
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col4 IS NOT NULL)

or maybe you wanted something like this???

SELECT
	MAX(col1) AS col1,
	MAX(col2) AS col2,
	MAX(col3) AS col3,
	Max(col4) AS col4
FROM
(
	SELECT col1, '' as col2, '' as col3 ,'' as col4
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col1 IS NOT NULL)

	UNION ALL

	SELECT '', col2, '', ''
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col2 IS NOT NULL)

	UNION ALL

	SELECT '', '', col3, ''
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col3 IS NOT NULL)

	UNION ALL

	SELECT '', '', '', col4
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col4 IS NOT NULL)
) AS Temp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-21 : 16:41:29

quote:

Originally posted by Lamprey

quote:
Originally posted by pbeesley

Lamprey, your solution results in one column, "red"

SELECT col1
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col1 IS NOT NULL)

SELECT col2
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col2 IS NOT NULL)

SELECT col3
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col3 IS NOT NULL)

SELECT col4
FROM @Yak
WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col4 IS NOT NULL)

or maybe you wanted something like this???

SELECT
	MAX(col1) AS col1,
	MAX(col2) AS col2,
	MAX(col3) AS col3,
	Max(col4) AS col4
FROM
(
	SELECT col1, '' as col2, '' as col3 ,'' as col4
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col1 IS NOT NULL)

	UNION ALL

	SELECT '', col2, '', ''
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col2 IS NOT NULL)

	UNION ALL

	SELECT '', '', col3, ''
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col3 IS NOT NULL)

	UNION ALL

	SELECT '', '', '', col4
	FROM @Yak
	WHERE create_date = (SELECT MAX(create_date) FROM @Yak WHERE col4 IS NOT NULL)
) AS Temp

This looks what OP expected as he is looking for non-null values accross column.

pbeesley
Starting Member

4 Posts

Posted - 2008-08-21 : 18:31:52

Thank you, that did the trick!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-22 : 05:36:18

Another option...

select
    (select top 1 col1 from @Yak where col1 is not null order by create_date desc) as col1,
    (select top 1 col2 from @Yak where col2 is not null order by create_date desc) as col2,
    (select top 1 col3 from @Yak where col3 is not null order by create_date desc) as col3,
    (select top 1 col4 from @Yak where col4 is not null order by create_date desc) as col4

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources