last_value - 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

General SQL Server Forums

New to SQL Server Programming

last_value

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-10-09 : 12:06:42

SQL 2008

I have a table that looks like this:


doc_no    seq_no   cus_no
1234        0       ABC
1234        1       NULL
1234        2       NULL
4444        0       XYZ
4444        1       NULL

I'm trying to do a select statement that will fill in the NULL with the correct Customer.

I would like the following returned.


doc_no    seq_no   cus_no
1234        0       ABC
1234        1       ABC
1234        2       ABC
4444        0       XYZ
4444        1       XYZ

I tried:
SELECT doc_no, seq_no, last_value(cus_no) over (partition by doc_no order by seq_no) as cus_No
from ARSRVFIL

I receive the message: "last_value is not a recognized built in function name"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-09 : 12:21:48

Here is one way:

-- Sample data
DECLARE @Foo TABLE (doc_no INT, seq_no INT,   cus_no VARCHAR(50))
INSERT @Foo VALUES
(1234, 0, 'ABC'),
(1234, 1, NULL),
(1234, 2, NULL),
(4444, 0, 'XYZ'),
(4444, 1, NULL)

-- Select
SELECT
	f.doc_no,
	f.seq_no,
	t.cus_no
FROM
	@Foo f
INNER JOIN
	(
		SELECT DISTINCT doc_no, cus_no
		FROM @Foo
		WHERE cus_no IS NOT NULL
	) AS t
	ON f.doc_no = t.doc_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 12:59:37

if there will be only one NOT NULL value for cs_no in a group then this


SELECT doc_no,seq_no,MAX(cus_no) OVER (PARTITION BY doc_no) AS cus_no
FROM Table

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources