first 10 records

[Guest]

Hello
I have a table Table1: Dept, Date

Now I need to bring first 10 records per Dept while date is sorted descending.
How should I write this query?

Thanks,

 

[Guest]

Try this

SELECT M1.[date], M1.Dept
FROM TableName AS M1
WHERE M1.[date] In (SELECT Top 10 M2.[date]
FROM TableName as M2
WHERE M2.Dept=M1.Dept
ORDER BY M2.[date] Desc)

Change the field name "date" to another name, date is a resurved value in
access

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com) is a small sample database
called "TopQuery.mdb", which illustrates this.

Look at example 4 (Advanced TOP query: Display the top 3 Invoices for EACH
Account number)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

SELECT TOP 10 EventSched.Event, EventSched.Appointment
FROM EventSched
ORDER BY EventSched.Appointment DESC;

 

[Guest]

It seems working however sometime I see 12 records instead of 10. Why is that?

Try this

SELECT M1.[date], M1.Dept
FROM TableName AS M1
WHERE M1.[date] In (SELECT Top 10 M2.[date]
FROM TableName as M2
WHERE M2.Dept=M1.Dept
ORDER BY M2.[date] Desc)

Change the field name "date" to another name, date is a resurved value in
access

Hello
I have a table Table1: Dept, Date

Now I need to bring first 10 records per Dept while date is sorted descending.
How should I write this query?

Thanks,

 

[Douglas J Steele]

The TOP predicate doesn't choose between equal values. In the preceding
example, if the tenth, eleventh and twelfth dates the same (or the ninth,
tenth, eleventh and twelfth, or ....), the query will return 12 records.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It seems working however sometime I see 12 records instead of 10. Why is that?

Try this

SELECT M1.[date], M1.Dept
FROM TableName AS M1
WHERE M1.[date] In (SELECT Top 10 M2.[date]
FROM TableName as M2
WHERE M2.Dept=M1.Dept
ORDER BY M2.[date] Desc)

Change the field name "date" to another name, date is a resurved value in
access

Hello
I have a table Table1: Dept, Date

Now I need to bring first 10 records per Dept while date is sorted descending.
How should I write this query?

Thanks,

 

[Roger Carlson]

As Doug says, a Top predicate will give you more than the requested number
if there are duplicates. The way around this is to make the main query a
Totals query. It won't work with your table as is, but if you added an
autonumber field (called ID for purposes here), the following query should
work. Caution, untested:

SELECT M1.[date], M1.Dept, Max(M1.ID) as MaxOfID
FROM TableName AS M1
GROUP BY M1.date, M1.dept
HAVING M1.[date] In (SELECT Top 10 M2.[date]
FROM TableName as M2
WHERE M2.Dept=M1.Dept
ORDER BY M2.[date] Desc, M2.ID Desc)
ORDER BY M1.date, M1.Dept

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

It seems working however sometime I see 12 records instead of 10. Why is that?

Try this

SELECT M1.[date], M1.Dept
FROM TableName AS M1
WHERE M1.[date] In (SELECT Top 10 M2.[date]
FROM TableName as M2
WHERE M2.Dept=M1.Dept
ORDER BY M2.[date] Desc)

Change the field name "date" to another name, date is a resurved value in
access

Hello
I have a table Table1: Dept, Date

Now I need to bring first 10 records per Dept while date is sorted descending.
How should I write this query?

Thanks,