select top 1 accross a whole set

Question

select top 1 accross a whole set

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
January 19, 2010 at 8:01 am

#386133

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

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.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 4

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 7

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.

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 8

attached if the result set. Highlighted is what should be returned.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 9

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 10

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 11

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

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

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', null
union
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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 14

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', null
union
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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]