Solved First() and Last() seem to be wrong way round (1 Viewer)

[Martin Beney]

Hi people,
I am sure I am being massively stupid but I have a problem with a very simple (testing only) query.

The query:
SELECT [Recipes plus category summary].[Recipe ID], [Recipes plus category summary].Expr1
FROM [Recipes plus category summary]
ORDER BY [Recipes plus category summary].[Recipe ID], [Recipes plus category summary].Expr1;
Returns (correctly):
Query2 Query2

Recipe ID	Expr1
68469​	03 Meal Type||100
68469​	03 Meal Type||49
68469​	03 Meal Type||64
68469​	15 Component||29

But when I add a group by clause to return the FIRST row of this:
SELECT [Recipes plus category summary].[Recipe ID], First([Recipes plus category summary].Expr1) AS FirstOfExpr1
FROM [Recipes plus category summary]
GROUP BY [Recipes plus category summary].[Recipe ID]
ORDER BY [Recipes plus category summary].[Recipe ID], First([Recipes plus category summary].Expr1);
It Returns:
Query2 Query2

Recipe ID	FirstOfExpr1
68469​	15 Component||29

Which is blatantly the LAST record.

Please what am I doing wrong?

Regards
Martin

 

[Josef P.]

Which is blatantly the LAST record.

... in the query, but probably not in the stored table data.

example:
create a table with num primary key (no autonumber)
Insert records in this order in PK data field: 3, 2, 1
select first(id) as FirstIdinTab from YourTestTable => 3

now compact your db:
select first(id) as FirstIdinTab from YourTestTable => 1

 

[CJ_London]

in this situation, first (and last) is returning from the unordered recordset, i..e

SELECT [Recipes plus category summary].[Recipe ID], [Recipes plus category summary].Expr1
FROM [Recipes plus category summary]

And potentially will vary because records are stored randomly

if you need first, then do another query based on query 2

 

[Martin Beney]

Hi,
Thanks. The record is indeed the first in the source table. I thought that first referred to the order in the query! That makes it rather hard to get at the items I want,,,,

Guess it down to write it myself in code - bother.

Thanks again for the info.
Martin

 

[Josef P.]

It's not complicated:

select
    D.*
from
    (
        select [Recipe ID], Min(YourFieldToSort) as MinXYZ from YourDataSource group by [Recipe ID]
    ) as X
    inner join
    YourDataSource as D ON D.[Recipe ID] = X.[Recipe ID] and D.YourFieldToSort = X.MinXYZ

 

[ebs17]

First and Last condition an order, at the time of these aggregations. The sort statement is executed later than the grouping and aggregating and does not matter for a desired order.

If you think the original order from [Recipes plus category summary] could be used: The Group operation could internally do a reordering.

First and Last are very often unsafe candidates. It is better to use Min and Max to access a usable characteristic for an order, such as a sequential number or a timestamp.

SELECT TOP 1 *
FROM [Recipes plus category summary]
ORDER BY [???]

With a TOP-X design you can use a sort directly and thus determine the first record or the last record (in case of descending sort) directly.
TOP as output operation acts after sorting.

 

[Martin Beney]

DOH!
Min and max solve it.

I am not having a good day!!

Many thanks guys
Martin

 

[The_Doc_Man]

Martin, just for the record and as a learning point: FIRST and LAST selection work perfectly well for sorted recordsets. However, tables are not inherently sorted. It's a long story, but basically you need something to enforce an order on your data source (which queries can do dynamically) in order for FIRST and LAST to have predictable results. In the abstract, FIRST and LAST on a raw table refer to the chronological order in which the records were last updated - because that was the last time they were stored. A query with an ORDER BY doesn't care when they were stored, but rather cares about something in the record itself that provides that order information.

 

[ebs17]

something to enforce an order on your data source (which queries can do dynamically) in order for FIRST and LAST to have predictable results

I would disagree with the optimism expressed above.
In practice, you will extremely rarely be able to ensure your required sequence. You just have to look at the order of query processing, maybe even an execution plan.
A JOIN, a GROUP BY ... these are comparative operations. If the SQL optimizer considers it useful, existing indexes are used. In the processing there can be extreme re-sortings which are not visible.
FIRST and LAST as aggregations go along with the grouping and are therefore executed far after the loading of the original data and far before the own sorting.

As written above, the own ORDER BY statement is quasi the very last thing that is executed to present a view to the outside.

 

[GPGeorge]

A few years ago I recorded a video on the differences between "First()/Last()" and "Min()/Max()" to illustrate the points made above.

 

[The_Doc_Man]

In practice, you will extremely rarely be able to ensure your required sequence.

Perhaps, but without SOME sort of ordering effort, you will almost NEVER be able to ensure a give sequence.

If the SQL optimizer considers it useful, existing indexes are used.

Is that true for native Access tables? I didn't see anything in the original post or subsequent replies to suggest an external SQL backend.

 

[ebs17]

Everything that offers a MoveFirst, MoveNext, MoveLast or any other targeted jumping like a recordset has a fixed order.
A query itself does not have that and cannot provide that. A maximum value, however, does not care about sequences, it can simply be determined with a simple function in the context of mass data processing (in an unordered set).

 

[The_Doc_Man]

A query itself does not have that

Until you open it, if the query has an ORDER BY clause. Then the query DOES have a fixed order.

You do nothing with closed queries except maybe edit them in design mode.

 

[ebs17]

If you open the query via OpenQuery and go to the datasheet view, you have a layout that corresponds to a recordset. There you have a fixed order and could go directly to the 5th record as an example.
Jumping to the 5th record is not possible via the correct query as SQL statement.

In a query, the vast majority of operations are not interested in an order, mass data processing takes place.
JOIN, WHERE, GROUP BY, HAVING ... nothing cares about sorting. But these operations are influenced by the SQL optimizer and its generated execution plan. Existing indexes are used if possible, their use will generate completely different sequences of records.

 

[The_Doc_Man]

Jumping to the 5th record is not possible via the correct query as SQL statement.

Unless you were doing some kind of ordered sub-query that involves contiguous numbering, you CANNOT jump to the 5th record because there is no syntax for that. Remember that all simple SQL queries, whether SELECT-class or action-class, are based on SET theory for which everything is supposed to be done as though it all occurred simultaneously over the whole set. OK, we know it can't actually BE simultaneous - but the language specification is such that there is no implied order of overall operation. Ordering must be explicit. A DAO or ADO recordset can do things for which Movexxxx as a way to explore a recordset makes sense. However, in an SQL statement, in the absence of an ordered subquery that correlates to the main recordset in some way, jumping to ANY record is syntactically impossible.