Query wit last time record

[pikard]

i have a form
with a date field

I have a table with products within and their date registration and code.

What I want to do is generate a record list, that will take each record and the MOST recent ie. taking the record with the most up to date registration with a given code.

How could i structure this in my query criteria?


example:

if the table is:

01 - pippo - 01/01/2005
01 - verdi - 23/08/2005
01 - blu - 24/08/2005
02 - giallo - 22/08/2005
02 - arancio - 27/08/2005
03 - marrone - 22/07/2004


the result of the query (if the search date is 28/08/2005)

01 - blu - 24/08/2005
02 - arancio - 27/08/2005
03 - marrone - 22/07/2004

 

[DotNetGnat]

your results are confusing...can you correct and post back or explain more....

try something like this:

SELECT ID, Name, Date FROM MyTable A
WHERE A.Date IN (SELECT TOP 1 Date FROM
MyTable B WHERE A.ID=B.ID ORDER BY Date DESC)

-DNG

 

[pikard]

I explain more

if the table is:

code - name - registration date

01 - pippo - 01/01/2005
01 - verdi - 23/08/2005
01 - blu - 24/08/2005
02 - giallo - 22/08/2005
02 - arancio- 27/08/2005
03 - marrone- 22/07/2004


the result of the query (by search date: 28/08/2005)

01 - blu - 24/08/2005

(The record with date <= search date and maximum registratione date with code 01)

02 - arancio - 27/08/2005

(The record with date <= search date and maximum registratione date with code 02)

03 - marrone - 22/07/2004

(The record with date <= search date and maximum registratione date with code 03)

 

[DotNetGnat]

did you try the query i suggested...

may be a little modification...


SELECT ID, Name, Date FROM MyTable A
WHERE A.Date IN (SELECT TOP 1 Date FROM
MyTable B WHERE A.ID=B.ID WHERE B.Date BETWEEN #21-08-2005# AND #28-08-2005 ORDER BY Date DESC)

Of course in the above query it will return the most recent date one among each ID but with in only one week gap of the search date...

try it out and tweak it to your needs...

-DNG

 

[PHV]

Another way:
SELECT A.code, A.name, A.[registration date]
FROM yourTable AS A INNER JOIN (
SELECT

, Max([registration date]) AS LastDate FROM yourTable
WHERE [registration date]<=#2005-08-28# GROUP BY [code]
) AS L ON A.code=L.code AND A.[registration date]=L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[monkeysee]

PHV or DotNetGnat

I have a similar problem. I tried your suggestions on the above inquiry, but can't see to get it to work. I have a table that tracks Inspections of Restaurants. I also want the query to return only the last inspection date for each facility. My query so far is this:

SELECT Inspections.InspectionID, Inspections.TypeID, Inspections.InspectorID, Inspections.InspectionDate, Inspections.FacilityID
FROM Inspections
ORDER BY Inspections.InspectionDate DESC;


Also, I must add that there maybe a case where the value in the inspection date field will be null.

Thanking you SO VERY MUCH IN ADVANCE!

 

[DotNetGnat]

try this:

SELECT A.InspectionID, A.TypeID, A.InspectorID, A.InspectionDate, A.FacilityID
FROM Inspections A
WHERE A.InspectionDate IN(SELECT Top 1 InspectionDate FROM Inspections B WHERE A.FacilityID=B.FacilityID
ORDER BY InspectionDate DESC);

-DNG

 

[PHV]

Have you tried this ?
SELECT A.InspectionID, A.TypeID, A.InspectorID, A.InspectionDate, A.FacilityID
FROM Inspections AS A INNER JOIN (
SELECT FacilityID, Max(InspectionDate) AS LastDate FROM Inspections GROUP BY FacilityID
) AS L ON A.FacilityID = L.FacilityID AND A.InspectionDate = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886