Selecting last non-NULL value in date range

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

General SQL Server Forums

New to SQL Server Programming

Selecting last non-NULL value in date range

Author

Topic

sql_user1000
Starting Member

3 Posts

Posted - 2013-07-10 : 07:02:02

Hi everyone,

I'm trying to construct code that will return the last non-NULL value in a column containing daily records.

For E.G. I want to know what the LAST value of Description field when it is not NULL, AND the Date is within the range t=1 to t=5 => i.e. "Dog" in the below example:

Date Description
1 NULL
2 NULL
3 Cat
4 Cat
5 Dog
6 NULL
7 NULL
8 Mouse
9 NULL
10 NULL

Please could anyone suggest how this can be achieved?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 08:14:00

[code]SELECT TOP (1) description FROM YourTable
WHERE date <= 5 AND description IS NOT NULL
ORDER BY date DESC [/code]

sql_user1000
Starting Member

3 Posts

Posted - 2013-07-10 : 08:52:55

quote:

Originally posted by James K

SELECT TOP (1) description FROM YourTable
WHERE date <= 5 AND description IS NOT NULL 
ORDER BY date DESC

Hi James,

Thank you very much for your code! Is top(1) still able to distinguish a value in a non-numerical column? In this case the last value?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 08:59:29

It looked like your DATE column is numeric, and we are sorting by that column. So you should be ok.

Even if it is not numeric, it would still be able to sort and pick the top 1. But, you have to be aware that string sort may not always give you the results you expect. For example, the query below returns 9 rather than 1100 because of the way string sort behaves. IF the data type was numeric you would have gotten 1100 instead.

CREATE TABLE #tmp (v VARCHAR(32));
INSERT INTO #tmp VALUES ('1100'),('9');

SELECT TOP (1) v FROM #tmp ORDER BY v DESC 

DROP TABLE #tmp;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 09:02:52

quote:
Originally posted by sql_user1000

quote:
Originally posted by James K

SELECT TOP (1) description FROM YourTable
WHERE date <= 5 AND description IS NOT NULL
ORDER BY date DESC

Hi James,

Thank you very much for your code! Is top(1) still able to distinguish a value in a non-numerical column? In this case the last value?

TOP 1 will give you with top most record based on order criteria (in this case descending order of date field). the description IS NOT NULL condition will ensure you consider only NOT NULL values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sql_user1000
Starting Member

3 Posts

Posted - 2013-07-10 : 09:56:36

Thank you very much James and Visakh for your kind help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources