SELECT DISTINCT problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT DISTINCT problem

Author

Topic

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-11-30 : 15:34:36

I have a table with customer data, in it includes:


name, address1, address2, address3, city, st, zip, bal_due

Dont ask me I didnt create this, but all addresses are on address3.

I need to select distinct address3, but sort by largest balance due [bal_due], and still show customer info.

I am sending out notices to customers, but I want the list to be as clean as possible. customers have multiple balances, but only needs to be ordered by the highest balance.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-30 : 15:37:32

select name, address3, city, st, zip, max(bal_due) as Max_Bal_Due
from Yourtable
group by name, address3, city, st, zip

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-11-30 : 17:28:13

THANKS FOR THE TIP ON THE MAX(BAL_DUE)

HOW DO I GET A DISTINCT RESULT ON ONE FIELD ONLY [ADDR_3], BUT PULL ADDITIONAL FIELDS, LIKE NAME, CITY, ST ETC.?

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-11-30 : 17:41:25

will i have to make a VIEW of distinct addresses? and use join to table get the info i want?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-03 : 08:02:50

You have multiple customers at the same address?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-03 : 08:42:43

1sqlover -- you need to show a specific example that demonstrates exactly what you are trying to do. right not it is all guesswork as to what you actually want. Ask a clear, precise, specific question and you will have a much better chance of getting a good answer. Makes sense, right?

- Jeff
http://weblogs.sqlteam.com/JeffS

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-12-04 : 09:29:46

Ok, here is the data I have.

FirstName,LastName,Addr1,Addr2,Addr3(Note:this is the address),City,ST,ZIP,Acct,Bal_Due
John,Doe,%Mary Doe,,123 N. Main,Anywhere,ST,12345,987654321-01,1000.00
John,Doe,,,123 N. Main,Anywhere,ST,12345,987654321-02,500.00
Jane,Doe,,,321 S. Broadway,Anywhere,ST,12345,987766553-01,1500.00
Mark,Smith,,,151 E. 1st,Anywhere,ST,12345,123456789-01,2000.00
Jane,Doe,,,321 S. Broadway,Anywhere,ST,123456,987766553-02,300.00

I want to create a query that will select distinct records based on [addr3], a bonus in the query would be to SUM all the balances due for [addr3].

Thanks
(Sorry about not being specific initially, I hope this helps.)

epoh

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-12-04 : 09:30:43

Notice there are two customers John Doe & Jane Doe with more than one Bal_Due but yet the same [addr3]

epoh

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-04 : 12:29:21

declare @t table (
FirstName varchar(25),
LastName varchar(25),
Addr1 varchar(25),
Addr2 varchar(25),
Addr3 varchar(25),
City varchar(25),
ST varchar(25),
ZIP varchar(25),
Acct varchar(25),
Bal_Due float
)

insert into @t
select 'John','Doe','Mary Doe','','123 N. Main','Anywhere','ST','12345','987654321-01',1000.00
union all select 'John','Doe','','','123 N. Main','Anywhere','ST','12345','987654321-02',500.00
union all select 'Jane','Doe','','','321 S. Broadway','Anywhere','ST','12345','987766553-01',1500.00
union all select 'Mark','Smith','','','151 E. 1st','Anywhere','ST','12345','123456789-01',2000.00
union all select 'Jane','Doe','','','321 S. Broadway','Anywhere','ST','123456','987766553-02',300.00

select Firstname, LastName, addr3, sum(bal_due) from @t group by Firstname, LastName, addr3

If this is not what you want post your expected results

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-12-04 : 18:17:50

That is what I am wanting to do.

So how do I do it using my data?

Thanks

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-12-04 : 19:14:32

OOOOPS :(, I MADE A MISTAKE I WAS LOOKING AT A VIEW WHEN I MADE THIS THE ACTUAL TABLES ARE LIKE THIS

Ok, here is the data with the tables.
------------------------------------------------------------
[MASTER](TABLE)

FirstName,LastName,Addr1,Addr2,Addr3(Note:this is the address),City,ST,ZIP,ACCT_NUM
John,Doe,%Mary Doe,,123 N. Main,Anywhere,ST,12345,987654321-01
John,Doe,,,123 N. Main,Anywhere,ST,12345,987654321-02
Jane,Doe,,,321 S. Broadway,Anywhere,ST,12345,987766553-01
Mark,Smith,,,151 E. 1st,Anywhere,ST,12345,123456789-01
Jane,Doe,,,321 S. Broadway,Anywhere,ST,123456,987766553-02

------------------------------------------------------------
Here is the second table
[ACCT_INFO](TABLE)


ACCT_NUM,CUST_NAME**,BAL_DUE
987654321-01,DOE, JOHN, 1000.00
987654321-02,DOE, JOHN, 500.00
987766553-01,DOE, JANE, 1500.00
123456789-01,SMITH, MARK, 2000.00
987766553-02,DOE, JANE,300.00


**CUST_NAME IS LASTNAME FOLLOWED BY A COMMA THEN FIRSTNAME

I want to query that will select distinct records based on [addr3](MASETER), a bonus in the query would be to SUM all the

balances due for [addr3](ACCT_INFO).

Thanks
(Sorry about not being specific initially and providing the wrong info, I hope this helps.)

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-05 : 07:40:29

I still do not see an expected result.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-12-05 : 09:28:43

DATA:
---------------------------
FirstName,LastName,Addr1,Addr2,Addr3(Note:this is the address),City,ST,ZIP,ACCT_NUM
John,Doe,%Mary Doe,,123 N. Main,Anywhere,ST,12345,987654321-01
John,Doe,,,123 N. Main,Anywhere,ST,12345,987654321-02
Jane,Doe,,,321 S. Broadway,Anywhere,ST,12345,987766553-01
Mark,Smith,,,151 E. 1st,Anywhere,ST,12345,123456789-01
Jane,Doe,,,321 S. Broadway,Anywhere,ST,123456,987766553-02

------------------------------------------------------------
Here is the second table
[ACCT_INFO](TABLE)

ACCT_NUM,CUST_NAME**,BAL_DUE
987654321-01,DOE, JOHN, 1000.00
987654321-02,DOE, JOHN, 500.00
987766553-01,DOE, JANE, 1500.00
123456789-01,SMITH, MARK, 2000.00
987766553-02,DOE, JANE,300.00
----------------------------------------------------
EXPECTED RESULT:
[FIRSTNAME],[LASTNAME],[SUM_AMT_DUE],[ADDR3],[CITY],[ST],[ZIP]
JOHN,DOE,1500.00,123 N. Main,Anywhere,ST,12345
JANE,DOE,1800.00,321 S. Broadway,Anywhere,ST,12345
MARK,SMITH,2000.00,151 E. 1st,Anywhere,ST,12345

----------------
I WOULD LIKE TO SEE THE INDIVIDUAL NAMES AND ADDRESSES WITH THE SUM OF BALANCES

Thanks.

epoh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 09:52:48

[code]-- Prepare sample data
DECLARE @Data TABLE (FirstName VARCHAR(20), LastName VARCHAR(20), Addr1 VARCHAR(20), Addr2 VARCHAR(20), Addr3 VARCHAR(20), City VARCHAR(20), ST VARCHAR(20), ZIP VARCHAR(20), ACCT_NUM VARCHAR(20))

INSERT @Data
SELECT 'John', 'Doe', 'Mary Doe', NULL, '123 N. Main', 'Anywhere', 'ST', '12345', '987654321-01' UNION ALL
SELECT 'John', 'Doe', NULL, NULL, '123 N. Main', 'Anywhere', 'ST', '12345', '987654321-02' UNION ALL
SELECT 'Jane', 'Doe', NULL, NULL, '321 S. Broadway', 'Anywhere', 'ST', '12345', '987766553-01' UNION ALL
SELECT 'Mark', 'Smith', NULL, NULL, '151 E. 1st', 'Anywhere', 'ST', '12345', '123456789-01' UNION ALL
SELECT 'Jane', 'Doe', NULL, NULL, '321 S. Broadway', 'Anywhere', 'ST', '123456', '987766553-02'

DECLARE @Info TABLE (ACCT_NUM VARCHAR(20), CUST_NAME VARCHAR(20), BAL_DUE MONEY)

INSERT @Info
SELECT '987654321-01', 'DOE, JOHN', 1000.00 UNION ALL
SELECT '987654321-02', 'DOE, JOHN', 500.00 UNION ALL
SELECT '987766553-01', 'DOE, JANE', 1500.00 UNION ALL
SELECT '123456789-01', 'SMITH, MARK', 2000.00 UNION ALL
SELECT '987766553-02', 'DOE, JANE',300.00

SELECT w.FirstName,
w.LastName,
w.SUM_AMT_DUE,
SUBSTRING(v.t, 1, v.p1 - 1) AS ADDR3,
SUBSTRING(v.t, v.p1 + 1, v.p2 - v.p1 - 1) AS City,
SUBSTRING(v.t, v.p2 + 1, v.p3 - v.p2 - 1) AS ST,
SUBSTRING(v.t, v.p3 + 1, 8000) AS Zip
FROM (
SELECT d.FirstName,
d.LastName,
SUM(i.Bal_Due) AS SUM_AMT_DUE
FROM @Data AS d
INNER JOIN @Info AS i ON i.Acct_Num = d.Acct_Num
GROUP BY d.FirstName,
d.LastName
) AS w
INNER JOIN (
SELECT q.FirstName,
q.LastName,
q.t,
CHARINDEX(CHAR(9), q.t) AS p1,
CHARINDEX(CHAR(9), q.t, CHARINDEX(CHAR(9), q.t) + 1) AS p2,
CHARINDEX(CHAR(9), q.t, CHARINDEX(CHAR(9), q.t, CHARINDEX(CHAR(9), q.t) + 1) + 1) AS p3
FROM (
SELECT FirstName,
LastName,
MIN(Addr3 + CHAR(9) + City + CHAR(9) + ST + CHAR(9) + ZIP) AS t
FROM @Data
GROUP BY FirstName,
LastName
) AS q
) AS v ON v.FirstName = w.FirstName AND v.LastName = w.LastName[/code]

E 12°55'05.25"
N 56°04'39.16"

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-12-05 : 09:59:38

Yes, yes! that is what i want. (very cool query by the way)

that works but how do i do this using my tables? I cant load all 12000 records into a variable I might as well type the list manually.

epoh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:21:11

What do the headers say?

PREPARE SAMPLE DATA

ignore that part and copy the "real" query... Of course you have to change column names and table names to the real names used in YOUR environment, of which I have no knowledge nor access to.

E 12°55'05.25"
N 56°04'39.16"

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2007-12-05 : 13:42:27

This is what I dont understand. How can I query my two tables: ACCT_INFO & CUST_INFO?

Do I have to declare variables, etc?

epoh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 13:44:48

Replace the @Data and @Info table names with the real table names in your environment.

E 12°55'05.25"
N 56°04'39.16"

1sqlover
Yak Posting Veteran

56 Posts