Solved

SQL Sub Query question

Posted on 2008-10-27
17
230 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
  • 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 59

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 59

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 59

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 59

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 59

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 59

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
string fuctions 4 26
sql server insert 12 30
SQL Server - Set Value of Multiple Fields in One Query 10 24
Need help with a Stored Proc on Sql Server 2012 4 9
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…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

773 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