?
Solved

SQL Sub Query question

Posted on 2008-10-27
17
Medium Priority
?
236 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 1400 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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 600 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

743 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