SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting last non-NULL value in date range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_user1000
Starting Member

3 Posts

Posted - 07/10/2013 :  07:02:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/10/2013 :  08:14:00  Show Profile  Reply with Quote
SELECT TOP (1) description FROM YourTable
WHERE date <= 5 AND description IS NOT NULL 
ORDER BY date DESC
Go to Top of Page

sql_user1000
Starting Member

3 Posts

Posted - 07/10/2013 :  08:52:55  Show Profile  Reply with Quote
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?

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/10/2013 :  08:59:29  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/10/2013 :  09:02:52  Show Profile  Reply with Quote
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
Go to Top of Page

sql_user1000
Starting Member

3 Posts

Posted - 07/10/2013 :  09:56:36  Show Profile  Reply with Quote
Thank you very much James and Visakh for your kind help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000