Pulling two most recent records 2

[beverlee]

I've searched the forums but can't find exactly what I need. In Access 2000 I have an Establishments table and an Inspections table. I need to find the two most recent inspections for each establishment. I've tried the "Top 2" option but it returns only the top two records from the entire table, not the top two for each establishment. I've also tried a query within a query without much luck. Any help is appreciated!

 

[Quehay]

This is a cool question, and you can do this with set logic.
*I assume business rule that no two inspections for a company will occur on the same date--if this is not the case you'll have to incorporate time comparison as well as date.

I did the following with Northwind Customers<Orders

1. Get latest Order for Customer:

[tt]SELECT Customers.CompanyName, Max(Orders.OrderDate) AS last_order
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName;
[/tt]

2. Get the Order for the Customer previous to the last:

[tt]SELECT Customers.CompanyName,
Max(Orders.OrderDate) AS next_to_last_order
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN last_order_each_customer ON (Customers.CompanyName = last_order_each_customer.CompanyName) AND (Orders.OrderDate < last_order_each_customer.last_order)
GROUP BY Customers.CompanyName;[/tt]

3. Join the results sorted by Customer, Order with a UNION ((an alias of &quot;last two orders&quot; would be better for second column):

[tt]SELECT
last_order_each_customer.CompanyName,
last_order_each_customer.last_order
FROM last_order_each_customer;

UNION

SELECT
next_to_last_order_customer.CompanyName, next_to_last_order_customer.next_to_last_order
FROM next_to_last_order_customer

ORDER BY 1,2 DESC[/tt]

Results:

[tt]CompanyName last_order
Alfreds Futterkiste 4/9/1998
Alfreds Futterkiste 3/16/1998
Ana Trujillo Emparedados y helados 3/4/1998
Ana Trujillo Emparedados y helados 11/28/1997
Antonio Moreno Taquería 1/28/1998
Antonio Moreno Taquería 9/25/1997
Around the Horn 4/10/1998
Around the Horn 3/16/1998
Berglunds snabbköp 3/4/1998
Berglunds snabbköp 2/6/1998[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[BNPMike]

Hey. I've given you a star. That's a lot of typing there.

 

[beverlee]

Quehay-
Awesome! I got it to pull the basics. Now I just need to add additional fields, put it into a report, etc! Thanks so much! I'll give you another star!

 

[r937]

the &quot;top 2&quot; option will work if you use it in a correlated subquery

[tt]select Customers.CompanyName
, This.OrderDate
from Customers
inner
join Orders This
on Customers.CustomerID = This.CustomerID
where This.OrderDate
in ( select top 2
OrderDate
from Orders
where CustomerID = This.CustomerID )[/tt]




rudy

http://r937.com/

SQL Consulting

 

[Quehay]

Cleaner still--I likes it!

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

Rudy:

Thanks for putting that up! I didn't know that the nested, correlated query could look for criteria that were present in the outer query.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[r937]

glad to help



rudy

http://r937.com/

SQL Consulting

 

[Quehay]

However when using your SQL against the same Northwind copy I get this result set:

[tt]
Company Name Order Date
Alfreds Futterkiste 25-Aug-1997
Alfreds Futterkiste 03-Oct-1997
Ana Trujillo Emparedados y helados 18-Sep-1996
Ana Trujillo Emparedados y helados 08-Aug-1997
Antonio Moreno Taquería 27-Nov-1996
Antonio Moreno Taquería 15-Apr-1997
[/tt]

Using the submission I put in above I get:
[tt]
CompanyName last_order
Alfreds Futterkiste 4/9/1998
Alfreds Futterkiste 3/16/1998
Ana Trujillo Emparedados y helados 3/4/1998
Ana Trujillo Emparedados y helados 11/28/1997
Antonio Moreno Taquería 1/28/1998
Antonio Moreno Taquería 9/25/1997
[/tt]

Not sure yet what accounts for the discrepancy...

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

It would appear that JET SQL does support this...here's a section from Help (&quot;subqueries&quot:

[tt]
You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. The Employees table is given the alias &quot;T1&quot;:

SELECT LastName,

FirstName, Title, Salary

FROM Employees AS T1

WHERE Salary >=

(SELECT Avg(Salary)

FROM Employees

WHERE T1.Title = Employees.Title) Order by Title;

[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[r937]

well, duh

i forgot the ORDER BY in the subquery!!!

rudy

http://r937.com/

SQL Consulting

 

[Quehay]

Correct--I was forgetting that TOP is entirely arbitrary vis a vis the result set (selects &quot;first n&quot; without reference to an order unless provided in results, unlike MAX/MIN--Access Help: If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.).

Just for reference purposes the order history in its entirety for the first company is: [tt]
Company Name Order Date
Alfreds Futterkiste 09-Apr-1998
Alfreds Futterkiste 16-Mar-1998
Alfreds Futterkiste 15-Jan-1998
Alfreds Futterkiste 13-Oct-1997
Alfreds Futterkiste 03-Oct-1997
Alfreds Futterkiste 25-Aug-1997[/tt]

To get the most recent dates for each company in the subquery AND show them last/next to last for each company in the results it requires ORDER BY in both (I'm not preaching here just sharing my own practice efforts):

[tt]
select
customers.companyname,
this.orderdate
from customers
inner join
orders this
on customers.customerid = this.customerid
where
this.orderdate in
(select top 2 orderdate
from orders
where customerid = this.customerid
order by orderdate desc)
order by 1,2 desc;
[/tt]

Cheers,

Jeff

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com