Last / Top (n)

  • Thread starter Thread starter Francezca
  • Start date Start date
F

Francezca

Hi,

I have a table that I am trying to recieve some records from...

A simplified example would be... I go to MDs and KFC and scan thei
shop front every day and all these scan are kept in a table calle
SCANS.

I want to be able to query the table and return the last 3 scan date
for each fast food outlet...

At the moment I use 'Last' to get the last one for each outlet, or
use TOP 3, to get the last three, but this is the last three for th
entire table not for each outlet in the table...

Im not sure how clearly I explained this, but if you understand and ca
help please do

:)

Thanks,

Francezc
 
You probably want to do this using a sub-query. That would look something like:

SELECT Outlet, ScanDate
FROM Table
WHERE ScanDate IN
(SELECT TOP 3 Dupe.ScanDate
FROM Table as Dupe
Where Dupe.Outlet = Table.Outlet
ORDER BY Dupe.ScanDate Desc)

Last does not do what you think (use Max instead). Last and First should be
treated as getting a random record in the group. Sometimes it will get the
actual Last record, but not necessarily.
 
Hi, I tried copying your query into my DB and changing to appropriat
table names but it came back with a syntax error...

I concede your point about using max and not last :P

Perhaps if I give you the actual table and field names....

table it called Patrols

the 'Outlet' field is called whereabouts

the 'Scandate' field is called Pdate

Im not familiar with 'Dupe' is it an SQL keyword or was it meant to b
another table/query?

Thanks

Francezc
 
Hi, I tried copying your query into my DB and changing to appropriat
table names but it came back with a syntax error...

I concede your point about using max and not last :P

Perhaps if I give you the actual table and field names....

table it called Patrols

the 'Outlet' field is called whereabouts

the 'Scandate' field is called Pdate

Im not familiar with 'Dupe' is it an SQL keyword or was it meant to b
another table/query?

Thanks

Francezc
 
Dupe was just an alias for the same table. We are trying to use the same table
twice within the query. To do that you need to give the second copy another
name so the SQL will know which instance you are referring to.

SELECT whereabouts, Pdate
FROM Patrols
WHERE PDate IN
(SELECT TOP 3 Dupe.Pdate
FROM Patrols as Dupe
Where Dupe.whereabouts = Patrols.whereabouts
ORDER BY Dupe.Pdate Desc)

If you are getting a syntax error, it usually hints at the location of the
error. If this doesn't work, suggest you post the exact text of the error.
 
Back
Top