Last 3 Query BY DATE

R

robboll

What about a query that only displays the last three entries by date.
So instead of

Unit Value Date
a 10 09/18/2005
a 12 10/17/2005
a 14 11/11/2005
a 20 11/21/2005
a 19 12/23/2005
b 19 10/14/2005
b 14 12/17/2005
b 18 02/10/2006
b 20 03/13/2006
b 21 03/29/2006


We see:


Unit Value Date
a 14 11/11/2005
a 20 11/21/2005
a 19 12/23/2005
b 18 02/10/2006
b 20 03/13/2006
b 21 03/29/2006


Where it only displays the last three entries for each unit.


What I have so far is:


SELECT L.Unit, L.DATE, L.Value
FROM Sheet1 AS L
WHERE (((L.DATE) In (select top 3 L.DATE
from
Sheet1 M
WHERE
M.unit= L.unit
ORDER BY L.unit, L.Date)));


Any tips appreciated!

robboll
 
M

Michel Walsh

Hi,


You can either Rank, either Top. I prefer Rank, which is more "universal"
(even natively supplied in MS SQL Server 2005), and I prefer doing it with
an inner join, one of the more compact solution:


SELECT a.Unit, SUM(a.Value), a.Date
FROM myTable As a INNER JOIN myTable As b
ON a.unit=b.unit AND a.date <= b.date
GROUP BY a.Unit, a.date
HAVING COUNT(*) <= 3



Note that I SUM the value field, for a given pair (Unit, date) so if there
is duplicated (Unit, date), the value will be the sum for all occurrences of
that pair. Otherwise, would do just fine too: First, Last, Min, Max.



Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


given a record in tableA, COUNT the number of record, typically in tableB
since in tableA the group would get just one record if it was just for
tableA, so, COUNT the number of records in table B such that b.unit = a.unit
AND b.Date >= a.Date. If the count =1, then that record in tableA is the
maximum among all those with the same b.unit, maximum over the values under
all the dates for that a.unit value. If the count=2, that record is the
second max, and so on. Since tableA is tableB, this COUNT is so the RANK
(first, second, third, ... ) within each group. Assuming each group (a.Unit,
a.Date) is without duplicated value, that is.


Another way to see it is to take a record in tableA. Then, counts the number
of records having the same unit value but having a date >= to it. If you
find just one, that is the max, its is the first one, its rank is 1. If you
find two records, that is the second max, its rank is 2. And so on. Repeat
for all possible records, keep only those where the count <= 3.


Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top