Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Distinct Question

Status
Not open for further replies.
epoh

epoh

IS-IT--Management
Joined
May 8, 2006
Messages
73
Location
US
I am working on a query that has the following fields

T1.FirstName, T1.LastName, T1.SSN, T1.Date, T1.Address, T1.City, T1.ST, T1.ZIP, T2.Rep

sample data
T1.FirstName, T1.LastName, T1.SSN, T1.Date, T1.Address, T1.City, T1.ST, T1.ZIP, T2.Rep
John,Doe,500-22-1234,2010-12-10,123 N Main,Anytown,US,12345,1
John,Doe,500-22-1234,2010-11-10,123 N Main,Anytown,US,12345,1
John,Doe,500-22-1234,2008-08-13,222 Old Address Rd,Oldtown,MD,12345,1
Jane,Doe,511-33-4321,2010-12-10,123 N Main,Anytown,US,12345,1
James,Smith,412-11-7894,2009-11-30,123 N Broadway,Anytown,US,12345,1
James,Smith,412-11-7894,2009-11-30,123 N Broadway,Anytown,US,12345,1
Jimmy,Staton,510-44-4567,2010-01-20,456 N Broadway,Anytown,US,12345,1

As seen in the data sample there are multiple SSNs but really only one customer.

How can I sort based on newest date (to get the most recent address) and then by unique SSN to get the desired data below?

Desired sample data:
T1.FirstName, T1.LastName, T1.SSN, T1.Date, T1.Address, T1.City, T1.ST, T1.ZIP, T2.Rep
John,Doe,500-22-1234,2010-12-10,123 N Main,Anytown,US,12345,1
Jane,Doe,511-33-4321,2010-12-10,123 N Main,Anytown,US,12345,1
James,Smith,412-11-7894,2009-11-30,123 N Broadway,Anytown,US,12345,1
Jimmy,Staton,510-44-4567,2010-01-20,456 N Broadway,Anytown,US,12345,1

Thanks
 
Sort by date Sort by votes
Code: 
SELECT T1.FirstName
     , T1.LastName
     , T1.SSN
     , T1.Date
     , T1.Address
     , T1.City
     , T1.ST
     , T1.ZIP
     , T2.Rep
  FROM ( SELECT SSN
              , MAX(Date) AS latest
           FROM daTable
         GROUP
             BY SSN ) AS s
INNER
  JOIN daTable AS T1
    ON T1.SSN = s.SSN
   AND T1.Date = s.latest

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Oops, I goofed.

T1.FirstName,
T1.LastName,
T1.SSN,
T1.Address,
T1.City,
T1.ST,
T1.ZIP,
T2.Date,
T2.Rep
 
Upvote 0 Downvote
What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Code: 
--CUSTOMER INFO TABLE
SELECT 
T1.ACCTNO,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP,
T1.SSN,
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE

FROM CUST_INFO T1, VISIT_DETAIL T2
WHERE T1.ACCTNO = T2.ACCTNO
ORDER BY T2.APPT_DATE DESC

What is throwing me off is getting DISTINCT SSNs using the information provided in the last appointment date "APPT_DATE". Because it is possible for the address to change from appointment to appointment I want to use the latest appointment date for the customer based on their unique SSN. Note: SSN is more unique than ACCTNO.
 
Upvote 0 Downvote
Code: 
--CUSTOMER INFO TABLE
SELECT 
T1.ACCTNO,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP,
T1.SSN,
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE

FROM CUST_INFO T1 
INNER JOIN VISIT_DETAIL T2 ON T1.ACCTNO = T2.ACCTNO
INNER JOIN (
SELECT ACCTNO,MAX(APPT_DATE) AS LastDate FROM VISIT_DETAIL GROUP BY ACCTNO
) T3 ON T2.ACCTNO = T3.ACCTNO AND T2.APPT_DATE = T3.LastDate
ORDER BY T2.APPT_DATE DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I will try this tomorrow when I'm back at work
 
Upvote 0 Downvote
Okay, this is closer but not quite there.

I'm trying to get a distinct/unique SSN based on the following:

Latest VISIT_DETAIL.APPT_DATE AND VISIT_DETAIL.ACCT_NO
&
T2.ACCTNO = T1.ACCTNO
&
the DISTINCT T1.SSN that is in the same row as T1.ACCTNO
Code: 
[COLOR=blue]T1.ACCTNO,[/color]
[COLOR=blue]T1.SSN,[/color]
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE
 
Upvote 0 Downvote
And what is wrong with my suggestion ?
 
Upvote 0 Downvote
Nothing. After further review I just found multiple entries for the same customer. That's because the ACCTNO changes with each visit. I overlooked that.

So a 5 time visitor still appears multiple times in the list.
 
Upvote 0 Downvote
What I want is to use the current address (T1.ADDR) that was entered when the customer visits using the latest APPT/VISIT date (T2.APPT_DATE) using the subquery you suggested.

Finally taking only one SSN (T1.SSN) where T1.ACCTNO = T2.ACCTNO
 
Upvote 0 Downvote
Perhaps this ?
Code: 
--CUSTOMER INFO TABLE
SELECT
T1.ACCTNO,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP,
T1.SSN,
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE
FROM CUST_INFO T1
INNER JOIN VISIT_DETAIL T2 ON T1.ACCTNO = T2.ACCTNO
INNER JOIN (
SELECT C.SSN,MAX(V.APPT_DATE) AS LastDate FROM CUST_INFO C INNER JOIN VISIT_DETAIL V ON C.ACCTNO=V.ACCTNO GROUP BY C.SSN
) T3 ON T2.APPT_DATE = T3.LastDate
WHERE T1.SSN = T3.SSN
ORDER BY T2.APPT_DATE DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question Question
SELECT COUNT distinct
Replies
5
Views
720
sal21
sal21
BobThornton
  • Locked
  • Question Question
windows command in a sql script
Replies
1
Views
605
carp
carp
jchewsmith
  • Locked
  • Question Question
SQL Syntax
Replies
1
Views
550
JarlH
JarlH
soushi01
  • Locked
  • Question Question
Nested For Loop update statement (pl/sql)
Replies
2
Views
438
soushi01
soushi01
jm89
  • Locked
  • Question Question
Does this function follow ansi sql standard?
Replies
4
Views
572
JarlH
JarlH

Part and Inventory Search

Sponsor

Back
Top