Solved

SQL Sub Query question

Posted on 2008-10-27
17
234 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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