Access SQL Using Top 5 Returning More Than 5 Results

Access SQL using TOP 5 returning more than 5 results?

This a known effect of the top directive in Access, but it's not very well known...

The top directive doesn't return the top n items, as one is easily led to believe. Instead it returns at least n distinct items determined by the ordering of the result.

In most cases it's the same, but in your example where the 5th to 8th items have the same ordering value, all of them are included. It returns the first five items, but then also all items that have the same ordering value as the fifth item.

If you don't apply any ordering to the table, all fields are considered, so if you have a unique field in the result the query will always return five items. The same of course if the unique field is included in the ordering.

Other dialects of SQL may behave differently. The top directive alone in T-SQL (SQL Server) for example never returns more than n items. However, by specifying the clauses with ties and order by along with top, one can observe the same behavior as in Access.

MS Access SQL - select specified amount of rows older than specified datetime

You say you've tried TOP clause, but it should work

SELECT TOP 30 * FROM Mails WHERE timeReceived  < '2012-02-01 12:00:00' ORDER BY timeReceived DESC

You must take this into account.

The top directive doesn't return the top n items, as one is easily led
to believe. Instead it returns at least n distinct items determined by
the ordering of the result.

Edit to clarify:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 2003
ORDER BY GradePointAverage DESC;

http://office.microsoft.com/en-us/access-help/results.aspx?qu=top&ex=1&origin=HA010256402

The TOP predicate does not choose between equal values. In the
preceding example, if the twenty-fifth and twenty-sixth highest grade
point averages are the same, the query will return 26 records.

So, no, rows with the same timestamp are not skipped. But if the 30th and 31th records(according to the order clause) have the same timestamp, both will be returned and you get 31 records.

If you want to force 30 records to be returned, you need to include the primary key into the Order By to differentiate between tied values:

SELECT TOP 30 * 
FROM Mails 
WHERE timeReceived  < '2012-02-01 12:00:00' 
ORDER BY timeReceived DESC, MailID ASC

Top n records per group sql in access

I had a similar problem a year ago: Top 3 per group including 0

Using the same approach, this will return the latest three dates for each LoginID - you may get more than three records if there are tied dates for the same LoginID.

SELECT  PR1.LogInID, PR1.Score, PR1.[Date Taken]
FROM    Progress AS PR1
WHERE   PR1.[Date Taken] IN (
                        SELECT TOP 3 PR2.[Date Taken]
                        FROM    Progress PR2
                        WHERE   PR2.LoginID = PR1.LoginID
                        ORDER BY PR2.[Date Taken] DESC
                        )
ORDER BY    LoginID, [Date Taken]

how to use LIMIT in query of MS ACCESS 2007

There is no LIMIT keyword in Access (if you use the JET engine). You can use TOP x to give the first x results.
Usage:

SELECT TOP 5 id FROM users ORDER BY joindate

From Microsoft Jet Database Engine Programmer's Guide - Chapter 4:

TOP N and TOP N PERCENT Predicates

Although you can use the WHERE and HAVING clauses to filter the selection of records, sometimes this isn't sufficient. For example, you may want to select all records where the state is CA, but only see the orders for the top 10 customers. Microsoft Jet provides TOP N and TOP N PERCENT predicates to limit the presentation of records after they're selected.

TOP N Predicate

You can use the TOP N predicate to specify that your query return only a specific number of records to your program:

Access 2010 Limit Query Results

What then is the Access equivalent of MySQL: LIMIT 1001, 25000 (ie return 25,000 results starting from the 1,001st)?

Unfortunately, in MS Access this isn't as straightforward as in MySQL.

In Access, you need to work with nested subqueries.

Here' an answer of mine where I'm showing how to build the correct SQL string for paging in C#:

How to do MS Access database paging + search?

Taking the SQL string from that answer and inserting your table name and column names will result in this query:

select [Cost Centre Code] from tblGL
where [Cost Centre Code] in (
    select top 25000 sub.[Cost Centre Code]
    from (
        select top 26000 tab.[Cost Centre Code]
        from tblGL tab
        where 1=1
        order by tab.[Cost Centre Code]
    ) sub
    order by sub.[Cost Centre Code] desc
)
order by [Cost Centre Code]

This eliminates at least the need for basic C# knowledge, but I'm afraid you'll still be confused in case you don't know how subqueries work :-)

The problem is:

Access has no built-in way to directly get 25000 rows, but skip the first 1000.

The only thing that's possible is to get the first X rows.

So I'm doing this (from the inside to the outside):

1. Load the first 26000 rows
   
   (1000 + 25000, because we want to skip 1000 rows and then load 25000)

2. From this dataset, load the first 25000 rows, but order descending.
   
   This will effectively load row 26000 to 1001 (in that order, because we ordered the rows descending!)

3. To get the rows in ascending order, just load from the table again (and order ascending!), but only the rows with the Cost Centre Codes from step 2.

Got it?

Yes, it looks intimidating at first glance, but as soon as you "get" subqueries, it's actually not that difficult.

How to select top 10 in Access query?

select top 10 Name, Price
from MyTable
order by Price desc

Updated: @Fionnuala pointed out that:

"Access SQL selects matches, so it will select all items with the
same highest prices, even if this includes more than 10 records. The
work-around is to order by price and a unique field (column)."

So, if you have a unique product code column, add like so:

select top 10 Name, Price
from MyTable
order by Price desc, UniqueProductCode desc

How to select top 1 in Access query - and actually get it to work

Add that as a JOIN and try it, Access sql parsing may be busted (been there), try this:

SELECT 
    c.[LastName] as C1,
    c.[FirstName] as C2,
    sd.maxsaledate as C3,
    c.[ClientID] as C4
FROM 
    [Client] c
    left join (
      select clientid, max(SaleDate) as maxsaledate from transactions group by clientid
    ) sd on
    c.ClientID = sd.ClientID

How to select top (5) rows with specified Id and latest date?

As your DateOld_ field is of type ntext, you cannot do an order by on that. To sort the results based on that column, you need to convert the values in to datetime format.

You can execute the following query to fetch 5 rows with id_utente = 134 and sorted in descending order as per the time:

select top 5 * from table1 
   where id_utente = 134 
   order by convert(datetime, convert(varchar, dateold_)) desc;

SQLFiddle Example

---

Related Topics