January 19, 2010 at 8:01 am
I'm trying to get each first, last, zip who has the most data filled out accross the whole set. I only want one record returned per fist, last, zip and it must be the one w/ the most information filled out.
drop table #cust
create table #cust
(
firstNM varchar(100)
,lastNM varchar(100)
,zip int
,email varchar(100)
,ip varchar(100)
,addr varchar(100)
)
insert into #cust(firstNM , lastNM , zip , email ,ip,addr )
select 'jesse', 'harris', 33703, null,'174.75.224.23', null
union
select 'jesse', 'harris', 33703, null,null, 'frank st.'
union
select 'jesse', 'harris', 33703, null,null,null
union
select 'jesse', 'harris', 33703, 'email1@hsn.net',null, null
union
select 'jesse', 'harris', 33703, null,'444.75.224.23', 'ghost st.'
union
select 'sam', 'more', 33716, null,'174.75.224.23', null
union
select 'sam', 'more', 33716, null,null, 'frank st.'
union
select 'sam', 'more', 33716, null,null,null
union
select 'sam', 'more', 33716, 'email1@hsn.net','444.75.224.23', null
union
select 'sam', 'more', 33716, null,'444.75.224.23', null
union
select 'homes', 'seswal', 33716, null,null, null
union
select 'harry', 'good', 33716, null,'444.75.224.23', null
union
select 'harry', 'good', 33716, null,'999.75.224.23', null
--i want one record with the most data filled out per firstnm, lastnm, zip.
--so the above should return records 7 for jesse because he has both ip and addr filled in, rec 13 for sam because he has both email and ip, and rec 3 for homes because he is the only one w that firstnam, lastnm and zip. If there are two records who have the same first, last, and zip and they also have the same other columns populated then just pick one like the records for harry.
select *
from #cust
January 19, 2010 at 8:06 am
When adding the following three samples to your data, which one should be displayed (each one has values in two out of three columns)?
select 'fred', 'unsure', 33716, 'test@here.com','444.75.224.23', null
union
select 'fred', 'unsure', 33716, 'test@here.com',null, 'home'
union
select 'fred', 'unsure', 33716, null,'444.75.224.23', 'home'
January 19, 2010 at 8:12 am
When adding the following three samples to your data, which one should be displayed (each one has values in two out of three columns)? Either, but just one of them.
January 19, 2010 at 8:46 am
BaldingLoopMan (1/19/2010)
When adding the following three samples to your data, which one should be displayed (each one has values in two out of three columns)? Either, but just one of them.
How's this?
Tell me if it gives the output you require.
;WITH cte
AS ( SELECT firstNm ,
lastNm ,
zip ,
email ,
ip ,
addr ,
CASE WHEN email IS NOT NULL THEN 1
ELSE 0
END AS Cnt1 ,
CASE WHEN ip IS NOT NULL THEN 1
ELSE 0
END AS Cnt2 ,
CASE WHEN Addr IS NOT NULL THEN 1
ELSE 0
END AS Cnt3
FROM #cust
),
cteSum
AS ( SELECT Firstnm ,
lastnm ,
zip ,
ROW_NUMBER() OVER ( PARTITION BY firstnm, lastnm, zip ORDER BY Cnt1 + Cnt2 + Cnt3 DESC ) AS ID ,
email ,
ip ,
Addr /*,
Cnt1 ,
Cnt2 ,
Cnt3*/
FROM cte
)
SELECT *
FROM CteSum
WHERE ID = 1
Cheers,
J-F
January 19, 2010 at 8:53 am
i need one record returned per first, last, and zip.
The idea here is someone wants the data from this table. Well they want the records who have the most info in them. So for each first, last, and zip they want the record w/ the most info. If there are more than one record for a given first, last, zip w/ the all their info then they want just one of them.
January 19, 2010 at 8:57 am
I can't really figure out what you want. Please take your sample data and show me what the correct output would be. Also, include any additional info based on subsequent posts.
January 19, 2010 at 9:02 am
BaldingLoopMan (1/19/2010)
i need one record returned per first, last, and zip.The idea here is someone wants the data from this table. Well they want the records who have the most info in them. So for each first, last, and zip they want the record w/ the most info. If there are more than one record for a given first, last, zip w/ the all their info then they want just one of them.
Have you tried the code I provided you? It does exactly that, well, with the test data I had.
Cheers,
J-F
January 19, 2010 at 9:11 am
i'm trying to paste the ooutput from excel w the hioghlighterd records but i can't. Forgive me for being a newbie w/ the formatting of these posts.
January 19, 2010 at 9:15 am
attached if the result set. Highlighted is what should be returned.
January 19, 2010 at 9:18 am
BaldingLoopMan (1/19/2010)
attached if the result set. Highlighted is what should be returned.
Great, the query returns exactly that information.
Have you tried it?
Cheers,
J-F
January 19, 2010 at 9:23 am
Sorry i did not. I was hung up on the id=1 in the where so i assumed it was returning one rec. Sorry. I'm going to wrap my mind around this solution. This will be my first time toying w/ cte and row_number() over ( partition by. So I will have several questions i assume. I love learning new stuff.
Thanks
January 19, 2010 at 9:51 am
The solution has been successfully consumed. Thanks.
I did notice that when using the cte functionality you must not have any code between any of the cte code. So i suppose it has to be written from beginning to end. Load it, amnipulate it, then select it. Intersting stuff.
Thanks
January 19, 2010 at 11:10 am
I'm happy it helped! Yes, Cte are very useful, they act like subqueries, but in my opinion, are a lot easier to read, it's just a top down approach. It is true you cannot declare a CTE and select it later in the code, like a subquery needs to be in a current query only. It is really useful while using Row_Number or other functions to filter data based on certain criterias.
Thanks for the feedback,
Cheers,
J-F
January 19, 2010 at 11:05 pm
lmu92 (1/19/2010)
When adding the following three samples to your data, which one should be displayed (each one has values in two out of three columns)?
select 'fred', 'unsure', 33716, 'test@here.com','444.75.224.23', nullunion
select 'fred', 'unsure', 33716, 'test@here.com',null, 'home'
union
select 'fred', 'unsure', 33716, null,'444.75.224.23', 'home'
Hmmmm.... in such an example, why not take the "best" of all the rows to come up with a "complete" row?
--Jeff Moden
January 20, 2010 at 10:28 am
Jeff Moden (1/19/2010)
lmu92 (1/19/2010)
When adding the following three samples to your data, which one should be displayed (each one has values in two out of three columns)?
select 'fred', 'unsure', 33716, 'test@here.com','444.75.224.23', nullunion
select 'fred', 'unsure', 33716, 'test@here.com',null, 'home'
union
select 'fred', 'unsure', 33716, null,'444.75.224.23', 'home'
Hmmmm.... in such an example, why not take the "best" of all the rows to come up with a "complete" row?
That would actually require to check for identical values instead of using the ISNULL() function only. Other than that, I agree.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply