How to combine first and second select statements

The first select statement fills in nulls and the second select statement removes duplicates. How can I run the second SQL statement off of the first or is there a way to combined them?

Here is the SQL Fiddle: Fiddle SQL

FIRST SELECT

/*
Fill in null values with last record that is not null
*/
SELECT Id, FirstName, LastName,

ISNULL(Address,
(
SELECT TOP (1) Address
FROM test
WHERE (FirstName = MainTable.FirstName) AND (Address IS NOT NULL) AND (Address <> '')

ORDER BY Id DESC)

) AS Address

FROM test AS MainTable


Second SELECT

/*
Return a single record
*/
SELECT FirstName, LastName, MAX(Address)
FROM test
Group By FirstName, LastName


RESULT EXPECTED:

| 4, john, doe, 9Street, SC |
| 5, marry, doe, 78Street, DC |


4 Answers
4

If you're using SQL Server version 2012 or higher you can use last_value() to get the last value in a partition (here by firstname and lastname) of ordered rows (here by id).

last_value()

firstname

lastname

id

Unfortunately it doesn't allow to ignore NULLs. But we can fix this if we sort rows where the column of interest IS NULL before those where it isn't. We can do so by first ordering by a CASE returning a lower value, if the column IS NULL and a higher value, if it isn't. The second column we then ORDER BY is id.

NULL

IS NULL

CASE

IS NULL

ORDER BY

id

To get only one row per firstname and lastname a simple DISTINCT will do then, as the address and city is the same in any row sharing firstname and lastname.

firstname

lastname

DISTINCT

address

city

firstname

lastname

SELECT DISTINCT
t1.firstname,
t1.lastname,
last_value(t1.address) OVER (PARTITION BY t1.firstname,
t1.lastname
ORDER BY CASE
WHEN t1.address IS NULL THEN
-1
ELSE
1
END,
t1.id
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) address,
last_value(t1.city) OVER (PARTITION BY t1.firstname,
t1.lastname
ORDER BY CASE
WHEN t1.city IS NULL THEN
-1
ELSE
1
END,
t1.id
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) city
FROM test t1;


As I understand, you want latest (by Id) address and city that is not null, right? If so:

SELECT max(t1.id) as id, t1.firstName, t1.lastName, adr.address, cty.City
FROM test t1
cross apply (select top(1) address from test t2
where t1.FirstName = t2.FirstName and
t1.LastName = t2.LastName and
t2.address is not null
order by id desc) adr(Address)
cross apply (select top(1) city from test t2
where t1.FirstName = t2.FirstName and
t1.LastName = t2.LastName and
t2.city is not null
order by id desc) cty(City)
group by t1.firstName, t1.lastName, adr.address, cty.City;


If you are using SQL Server 2012 or higher, sticky bit answer looks to be the best one. If you are using lower version then you can check the below answer.

SQL Server 2012

sticky bit

SELECT
CAST((CASE
WHEN CHARINDEX('$', Id) > 0
THEN RIGHT(Id, CHARINDEX('$', REVERSE(Id)) - 1)
ELSE Id
END
) AS INT) AS Id,
Firstname, Lastname,
(CASE
WHEN CHARINDEX('$', Address) > 0
THEN RIGHT(Address, CHARINDEX('$', REVERSE(Address)) - 1)
ELSE Address
END
) AS Address,
(CASE
WHEN CHARINDEX('$', City) > 0
THEN RIGHT(City, CHARINDEX('$', REVERSE(City)) - 1)
ELSE City
END
) AS City
FROM (

SELECT A.Firstname, A.Lastname,
STUFF((
SELECT '$' + CAST(B.Id AS VARCHAR)
FROM test B
WHERE B.Firstname = A.Firstname
AND
B.Lastname = A.Lastname
For XML Path('')),1,1,'') AS Id,
STUFF((
SELECT '$' + B.Address
FROM test B
WHERE B.Firstname = A.Firstname
AND
B.Lastname = A.Lastname
For XML Path('')),1,1,'') AS Address,
STUFF((
SELECT '$' + B.City
FROM test B
WHERE B.Firstname = A.Firstname
AND
B.Lastname = A.Lastname
For XML Path('')),1,1,'') AS City
FROM test A
GROUP BY A.Firstname, A.Lastname
) T


I think title is wrong, it should be like "how to get latest value of different columns".

Also hope your table should contain EmpID or USERID so that index can be created on them.

EmpID

USERID

Or Table can be further normalised if possible.

Create table Table1(Empid int pk,FirstName,LastName)

Create table Table2(id int identity(1,1),EMPID FK,Adress,city)


Querying on these two tables will be very efficient.

SELECT DISTINCT t.Firstname
,t.Lastname
,t1.Address
,t2.City
FROM #test t
CROSS APPLY (
SELECT TOP 1 t1.Address
FROM #test t1
WHERE t1.Address IS NOT NULL
AND t.Firstname = t1.Firstname
AND t.Lastname = t1.Lastname
ORDER BY t1.id DESC
) t1
CROSS APPLY (
SELECT TOP 1 t2.City
FROM #test t2
WHERE t2.City IS NOT NULL
AND t.Firstname = t2.Firstname
AND t.Lastname = t2.Lastname
ORDER BY t2.id DESC
) t2


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.