Solved

SQL Sub Query question

Posted on 2008-10-27
17
232 Views
Last Modified: 2012-05-05
The top SQL statement in the code snippet below selects customer information and policy information for each customer in a database.

However there may be one than one policy for each customer so I want to select only the newest policy for each customer.

I think I need to have a subquery that returns the policyguid of the newest policy grouped by customer id.

The second SQL statement now includes a subquery that returns the entrydate of the latest policy grouped by customerid but really i need that statement to return the policyguid to make an exact match.

How do I get the subquery to return the policyguid of the latest record? If I amend the subquery to also return the policyguid then the sub query returns more records than just returning the max date alone.

Can anyone help?

SELECT        c.CustomerID, c.Address1, c.City, c.Region, c.PostalCode, c.Title, c.LastName, c.FirstName, c.Telephone, p.PolicyGUID, p.ProductCode, p.Cancelled, 
                         p.EntryDate
FROM            Customers AS c INNER JOIN
                         Policies AS p ON c.CustomerID = p.CustomerID
WHERE        (COALESCE (c.EmailAddress, '') = '') AND (p.Cancelled = 0)
ORDER BY c.CustomerID
 
----------------------
 
SELECT        c.CustomerID, c.Address1, c.City, c.Region, c.PostalCode, c.Title, c.LastName, c.FirstName, c.Telephone, p.PolicyGUID, p.ProductCode, p.Cancelled, 
                         p.EntryDate
FROM            Customers AS c INNER JOIN
                         Policies AS p ON c.CustomerID = p.CustomerID
WHERE        (COALESCE (c.EmailAddress, '') = '') AND (p.Cancelled = 0) AND (p.EntryDate IN
                             (SELECT        MAX(EntryDate) AS Expr1
                               FROM            Policies
                               GROUP BY CustomerID))
ORDER BY c.CustomerID

Open in new window

0
Comment
Question by:g-spot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
17 Comments
 
LVL 20

Accepted Solution

by:
chaitu chaitu earned 350 total points
ID: 22812040
try this

SELECT        c.CustomerID, c.Address1, c.City, c.Region, c.PostalCode, c.Title, c.LastName, c.FirstName, c.Telephone, p.PolicyGUID, p.ProductCode, p.Cancelled, 
                         p.EntryDate
FROM            Customers AS c INNER JOIN
                         Policies AS p ON c.CustomerID = p.CustomerID
WHERE        (COALESCE (c.EmailAddress, '') = '') AND (p.Cancelled = 0) AND (p.EntryDate IN
                             (SELECT        MAX(EntryDate) AS Expr1
                               FROM            Policies P1
 WHERE c.CustomerID = p1.CustomerID
                               GROUP BY CustomerID))
ORDER BY c.CustomerID

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22812090
What SQL platform and version are you on?
0
 

Author Comment

by:g-spot
ID: 22812116
SQL Server 2008 Express
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:g-spot
ID: 22812169
Hi chaituu

Youve added in "WHERE (c.CustomerID = CustomerID)" to the sub query

That seems to work. But im not sure why?
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22812205
you want MAX(EntryDate)  for the same customer that exists in  Policies table also.thats why i put same join in the sub query also.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 total points
ID: 22812362
Looks like you got a solution already, but since I made you tell me the version here another solution.  It uses row_number() in an over statement that partitions (groups) data by customer id with entry date descending and tags with a row number.  Last date for each customer will then be rowNum = 1, but you can still access all the data of that row since the overall query isn't a group by.
SELECT c.CustomerID, c.Address1, c.City, c.Region, c.PostalCode
, c.Title, c.LastName, c.FirstName, c.Telephone
, p.PolicyGUID, p.ProductCode, p.Cancelled, p.EntryDate 
FROM Customers AS c 
INNER JOIN (SELECT *, row_number() over (PARTITION BY CustomerID ORDER BY EntryDate DESC) AS rowNum FROM Policies) AS p 
ON c.CustomerID = p.CustomerID 
WHERE (COALESCE (c.EmailAddress, '') = '') AND (p.Cancelled = 0) AND (p.rowNum = 1) 
ORDER BY c.CustomerID 

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22812404
For chaituu's solution, it works as without the filter on the customerID to find the max date, you will find the maximum date for all customers and as long as a date is in that list it will show up; therefore, if a customer has a date in its policy list that matches the max date of another customer then it will show along with the customers own max date.  Check your original output, you probably had multiple rows for some customers if not all.

Chaituu's solution will fix that.
0
 

Author Comment

by:g-spot
ID: 22812524
Hi mwvisa1, thank you for your alternative solution and your further explanation of chaituu's solution.

I understand now.

0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22812530
yeah .mwvisa1 explianed better than me.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22812578
You did the good job of posting it. :)
Glad to help g-spot.  That is why we are here.
0
 

Author Comment

by:g-spot
ID: 22812645
I will accept mwvisa's solution as it was the first and the one I will be using.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22812671
You mean chaituu?
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22812668
no problems.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22812732
better split the points...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22812800
@chaituu, I think g-spot meant you there, as said entry was first.  Your entry is the first one. :) I will gladly take a small portion of split though. ;)
0
 

Author Comment

by:g-spot
ID: 22812857
Sorry, I meant I will award the points to chaituu (and some to mwvisa).

Thanks again
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22812885
good
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question