SQL Distinct Question

Question

SQL Distinct Question

epoh (IS/IT--Management)

(OP)

13 Dec 10 19:56

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

r937 (TechnicalUser) · Answer 1 · 2010-12-13 20:00:37

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

epoh (IS/IT--Management) · Answer 2 · 2010-12-13 20:18:12

Oops, I goofed.

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

PHV (MIS) · Answer 3 · 2010-12-13 20:29:50

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

epoh (IS/IT--Management) · Answer 4 · 2010-12-13 20:50:06

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.

PHV (MIS) · Answer 5 · 2010-12-13 21:17:38

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

epoh (IS/IT--Management) · Answer 6 · 2010-12-13 22:24:17

I will try this tomorrow when I'm back at work

epoh (IS/IT--Management) · Answer 7 · 2010-12-14 10:16:10

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

T1.ACCTNO,
T1.SSN,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE

PHV (MIS) · Answer 8 · 2010-12-14 12:02:57

And what is wrong with my suggestion ?

epoh (IS/IT--Management) · Answer 9 · 2010-12-14 13:46:58

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.

epoh (IS/IT--Management) · Answer 10 · 2010-12-14 14:11:19

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

PHV (MIS) · Answer 11 · 2010-12-15 09:30:32

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Come Join Us!

Posting Guidelines

SQL Distinct Question