×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Distinct Question

SQL Distinct Question

SQL Distinct Question

(OP)
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

RE: SQL Distinct Question

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

RE: SQL Distinct Question

(OP)
Oops, I goofed.

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

RE: SQL Distinct Question

(OP)

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.

RE: SQL Distinct Question

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?

RE: SQL Distinct Question

(OP)
I will try this tomorrow when I'm back at work

RE: SQL Distinct Question

(OP)
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

RE: SQL Distinct Question

And what is wrong with my suggestion ?

RE: SQL Distinct Question

(OP)
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.
 

RE: SQL Distinct Question

(OP)
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

RE: SQL Distinct Question

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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close