[AccessD] first five dates per year

Okay, simple table, ID (AutoNumber), SomeText (text field) and MyDate (Date
field).  I called this table tblTest.  The following SQL will show the first
five records for each year:
 
SELECT ID, SomeText, MyDate
FROM tblTest AS T1
WHERE ID In 
(SELECT TOP 5 ID
FROM tblTest
WHERE Year(MyDate)=Year(T1.MyDate)
ORDER BY MyDate);

The subquery pulls up a group of up to 5 ID's, which the main query then
uses to determine if it's ID should be displayed or not.  I used an ID
field, because if there are more then 5 dates (say you have 6 records for
January 1st in one year...) you would get more then 5 records back if you
made the IN statement compare the date field to the subquery.
 
Drew

-----Original Message-----
From: Pedro Janssen [mailto:pedro at plex.nl]
Sent: Wednesday, March 05, 2003 10:24 AM
To: AccessD at databaseadvisors.com
Subject: Re: [AccessD] first five dates per year


Thanks for your help Juan. 
This is an usefull function. Never noticed that is was present. But it gives
me only the Top 5 of the total of records.
I tried to give me the top5 dates each year with this function, but i can't
make it work
 
Drew, i don't have a criterium for the records i don't want to. I just need
the first 5 each year.
 
Pedro

----- Original Message ----- 
From: MastercafeCTV <mailto:mastercafe at ctv.es>  
To: accessd at databaseadvisors.com <mailto:accessd at databaseadvisors.com>  
Sent: Wednesday, March 05, 2003 12:30 AM
Subject: RE: [AccessD] first five dates per year

Yes simply use TOP 5 clausule in SQL Query , check SQL help in A2k the
command TOP and BOTTOM for the first or last in a query
 
Juan Menendez
Mastercafe SL
www.mastercafe.com <http://www.mastercafe.com> 
 

-----Mensaje original-----
De: accessd-admin at databaseadvisors.com
<mailto:accessd-admin at databaseadvisors.com>  [
mailto:accessd-admin at databaseadvisors.com
<mailto:accessd-admin at databaseadvisors.com> ]En nombre de Pedro Janssen
Enviado el: martes, 04 de marzo de 2003 23:14
Para: AccessD at databaseadvisors.com
Asunto: [AccessD] first five dates per year


Hello Group,
 
i have a table with 10000 records. I would like to filter out, by query, the
first 5 or 10 dates per different years.
Is this possible.
 
TIA 
 
Pedro Janssen

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030305/a48e2e36/attachment-0001.html>