Solved

SQL Sub Query question

Posted on 2008-10-27
17
228 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now