SQL Query Question

[Joe4]

Is there a simple way to do the following in one, succint SQL query? I know I can do it by breaking it up into two queries, but would like to get it down to one (hopefully simply) query.

Here is my table structure:
*Company ID
*Person_ID
Change_Date
Value
(there are other fields, but they are not important to the query, or part of the result set).

Here is what I would like to do:
For each Company_ID/Person_ID combination, I would like to return only the most recent record (based on Change_Date) and display the Value associated with that record.

For example, if I had:

123456789,111111111,07/01/2006,B
123456789,111111111,07/02/2006,C
123456789,111111111,06/30/2006,A
123456789,222222222,06/29/2006,A

It would return:

123456789,111111111,07/02/2006,C
123456789,222222222,06/29/2006,A

Thanks!

 

[SydneyGeek]

Hi jmiskey, without trying to get the SQL syntax correct from scratch I would do this visually.

1. Build the query that gives you the fields you want.
2. Make it a Totals query.
3. Group By on CompanyID, Person_ID and Value, and set Change_Date to Max or Last. You may also want to alias the Change_date field to LastChange or similar.

Don't quote me on this but the SQL should be something like

SELECT CompanyID, Person_ID, MaxOfChange_Date As LastChange, Value
FROM MyTable
GROUP BY CompanyID, Person_ID, Value;

Denis

 

[Joe4]

Denis,

I don't think the code/idea you provided will work, as including the Value in the Grouping will NOT give the expected results I posted (it could give multiple records for each Company_ID/Person_ID combination, when I only want one record for each Company_ID/Person_ID combination.

Any other ideas?

(By the way, I know I can do this in multiple queries in Access, but this is NOT in Access. It is a software program which uses SQL, which is why I am trying to do this as a single SQL query.)

 

[SydneyGeek]

Sounds like you may need a subquery.
Something like:

SELECT CompanyID, Person_ID, Change_Date, Value
FROM MyTable
WHERE Change_Date IN (SELECT CompanyID, Person_ID, MaxOfChange_Date As LastChange
FROM MyTable
GROUP BY CompanyID, Person_ID);

Denis

 

[Richard Schollar]

Hi

This is a slightly different way of doing the same:

SELECT T.Company_ID, T.Person_ID, T.Change_Date, T.Value
FROM Table1 AS T, (SELECT DISTINCT Person_ID, Max(Change_Date) As Max_Date FROM Table1  GROUP BY Person_ID) AS Q
WHERE T.Person_ID=Q.Person_ID And T.Change_Date=Q.Max_Date;

Best regards

Richard

 

[Joe4]

Denis,
There seems to be a problem with the code. I get an error that saying that is looking for an EXISTS statement within the SELECT.

Richard,
The code you posted does exactly what I need! Thank you!

Over the years, I have had the luxury of using Access query builder for most of my queries. Now that I am working directly in SQL, it looks like I will have to improve my subquery writing skills!

 

[SydneyGeek]

Found a solution. Assuming your table is called tblOrders, you effectively query an alias of the table with the restricted rows, then create a double join on CustomerID and ProductID --

SELECT tblOrders.*
FROM tblOrders 
WHERE tblOrders.PurchaseDate =
   (SELECT Max(PurchaseDate)
      FROM tblOrders AS Orders2
      WHERE Orders2.CustomerID = tblOrders.CustomerID AND Orders2.ProductID = tblOrders.ProductID);

On a 4-row sample very similar to yours, this returned the desired 2 rows. BTW, I know what you mean about losing the graphical query interface. I'm slowly getting weaned too, and the process is not entirely pretty

{EDIT} Hmmm... This looks remarkably like Richard's effort

Denis

 

[Joe4]

BTW, I know what you mean about losing the graphical query interface. I'm slowly getting weaned too, and the process is not entirely pretty

Yes, I know what you mean. I have gotten quite good at using the graphical interfaces, and even manipulating existing SQL (for VBA purposes), but have not written too much straight SQL yet. I do all right on the basic stuff, but those subqueries are going to take a bit of practice before I get comfortable with them!

Thanks to you and Richard for all the help!

 

[Richard Schollar]

Denis's query is a correlated sub-query whereas mine is a plain vanilla sub-query. Depending on the size of your dataset, you may find one or other syntax produces better performance.

I have never had the fortune to be good with Access or the query builder but when first introduced to SQL I thought 'Hey, this is great and it looks easy!'. 12 months down the line, I realise I have only barely started scratching the surface (and that's only for querying - no fancy make table stuff etc!) and there's an awful long way to go.

Good luck!

Richard