Limiting a few result from a query.

[DarkWorlds]

Ok right now I have an issue I was hoping to solve.

I currently have a query that pulls up 1.9 million people

It pulls the following and all of these are unique by the pull. No duplicates in the bunch.

Account_ID
Account_Status
Account_Type
Account_Holder_Name_First
Account_Holder_Name_Last
Account_Holder_Addr
Account_Holder_City
Account_Holder_State
Account_Holder_Zip
Account_Holder_Phone

So all of them have a unique account ID, but a person can have more than one account. We intend to call these people and just make sure info is up to date. So when I did a unique search on just Last_Name, Addr, Phone I got 1.2 million results.

I was hoping to keep the above info, but get rid of the 700k worth of info because we might call someone 2-3 times. Got any suggestions? I hope I was clear on what I needed. I just need to narrow it down a few results

 

[ptheriault]

Have you tried SELECT TOP 1?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[DarkWorlds]

If I

select Account_ID,
Account_Status,
Account_Type,
Account_Holder_Name_First,
Account_Holder_Name_Last,
Account_Holder_Addr,
Account_Holder_City,
Account_Holder_State,
Account_Holder_Zip,
TOP 1 Account_Holder_Phone

from...


That dies :/ Unless I went around it wrong?

 

[ptheriault]

Try this.

select TOP 1
Account_ID,
Account_Status,
Account_Type,
Account_Holder_Name_First,
Account_Holder_Name_Last,
Account_Holder_Addr,
Account_Holder_City,
Account_Holder_State,
Account_Holder_Zip,
Account_Holder_Phone

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[DarkWorlds]

Well that didnt help, it just got me 1 result... lol

I need all of them, just not the ones that have match last name, address, and phone.

I do thank you for your help and input

 

[ptheriault]

Oh, sorry.
Try a group by Last name, address, and phone.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Try this...

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   Table_Name
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
          [COLOR=blue]Select[/color] [COLOR=#FF00FF]Max[/color](Account_Id) [COLOR=blue]As[/color] Account_ID
          [COLOR=blue]From[/color]   TableName
          [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Account_Holder_Name_Last,
                 Account_Holder_Addr,
                 Account_Holder_Phone
          [COLOR=blue]Having[/color] [COLOR=#FF00FF]Count[/color](*) > 1
          ) [COLOR=blue]As[/color] A
          [COLOR=blue]On[/color] Table_Name.Account_Id = A.Account_Id

Untested.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DarkWorlds]

Thank for your quick reply, if I do that, I get many of these.

...invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[gmmastros]

Well... I created some test data, and it appears to work properly. This does, of course, assume that I understand what you want.

Here's how I tested this...

[COLOR=blue]Declare[/color] @Temp 
[COLOR=blue]Table[/color]   (Account_ID [COLOR=blue]Int[/color], 
        Account_Holder_Name_Last [COLOR=blue]VarChar[/color](20), 
        Account_Holder_Addr [COLOR=blue]VarChar[/color](20),
        Account_Holder_Phone [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'Bush'[/color], [COLOR=red]'White House'[/color], [COLOR=red]'12345'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, [COLOR=red]'Bush'[/color], [COLOR=red]'White House'[/color], [COLOR=red]'12345'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3, [COLOR=red]'Bush'[/color], [COLOR=red]'White House'[/color], [COLOR=red]'12345'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4, [COLOR=red]'Clinton'[/color], [COLOR=red]'New York'[/color], [COLOR=red]'56789'[/color])

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp [COLOR=blue]As[/color] Table_Name
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
          [COLOR=blue]Select[/color] [COLOR=#FF00FF]Max[/color](Account_Id) [COLOR=blue]As[/color] Account_ID
          [COLOR=blue]From[/color]   @Temp
          [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Account_Holder_Name_Last,
                 Account_Holder_Addr,
                 Account_Holder_Phone
          [COLOR=blue]Having[/color] [COLOR=#FF00FF]Count[/color](*) > 1
          ) [COLOR=blue]As[/color] A
          [COLOR=blue]On[/color] Table_Name.Account_Id = A.Account_Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DarkWorlds]

Thanks to all, but I am back

Well it worked, or atleast I think it did. It seems to have a low return of results. 467k out of 1.9 million. But I will assume its working right. I will double check a few results to make sure. But now I wanna throw a monkey wrench in this.

To this field, I want to add a id key to this.

SELECT 	(SELECT count(Account_ID) 
	FROM ACCOUNT_DATA AS x 
	WHERE x.Account_ID <= PD.Account_ID)AS Sequence,...

This from what I know should create the ID column I want. In fact it does. But the problem is, its that the numbers are all over the place and range from 1-1.9mill. So it seems its counting everything inluding the duplciates.

Using gmmastros above sample how could I do this?

 

[monksnake]

It wouldn't be an ID key if there are duplicate ID's

<.

 

[DarkWorlds]

It's from above, but ill explain.

Basically everyone has an account ID, and they can have multiple account ID's. What I wanted was the unique people behind the account ID's. So originally we had 2 million results. This got down to 400k.

So when I add the count to it, the numbers should be 1 – 400k right? The problem is that the numbers range from 1 – 2 million.

So like I said, its counting the records that have been eliminated, but its not showing them.

I am look for a way to have it count the records that are shown, not just all of them.

I am assuming I will have to take my original result, and put it into a temp table then have the increasing number field.

So anyone point me on the right path?

 

[DarkWorlds]

Ok I have a slight problem with the query after all. Like I said above the table has 1.9 million accounts. But trying to get the unique last name, address, and phone should get it to 1.2 mill or so. So with the help of this forum, this is my query.

SELECT	PD.Account_ID
	PD.Account_Type
	PD.Account_Holder_Name_First
	PD.Account_Holder_Name_Last
	PD.Account_Holder_Addr
	PD.Account_Holder_City
	PD.Account_Holder_State
	PD.Account_Holder_Zip
	PD.Account_Holder_Phone

FROM	PERSON_DATA AS PD

Inner Join (
	Select Max(Account_Id) As Account_ID
        From   PERSON_DATA
        Group By Account_Holder_Name_Last,
                 Account_Holder_Addr,
                 Account_Holder_Phone
        Having Count(*) > 1
        ) As A
        On PD.Account_Id = A.Account_Id

ORDER BY PD.Account_Holder_Zip, PD.Account_Holder_Name_Last, PD.Account_Holder_Addr

The problem is with this, I’m only getting 400k of results. This is off from every other number I’ve pulled up for what should be the results.

SELECT	DISTINCT Account_Holder_Name_Last,
	Account_Holder_Addr,
	Account_Holder_Phone

From	PERSON_DATA

This yields 1.4 million results. And

SELECT	Account_Holder_Name_Last, 
	Account_Holder_Addr, 
	Account_Holder_Phone	

FROM	PERSON_DATA

GROUP BY Account_Holder_Name_Last, 
	Account_Holder_Addr, 
	Account_Holder_Phone

This also gets the same results as the one above. So why does the main result get 400k but the same type of search gets a much higher number?
I hope I am missing something. I used all of the above info to get to this point, so that’s what I am referencing if you needed to know.

I look forward to any and all help.