CODEWITHSUNDEEP
ms-access
Jossy
Jossy

Reputation: 999

Why is Last selecting First in MS Access?

I have the follow SQL:

SELECT tbl_G_stats_atp.PK_G, tbl_G_stats_atp.InjuryCnt
FROM tbl_G_stats_atp
WHERE (((tbl_G_stats_atp.ID_A)=89) AND ((tbl_G_stats_atp.DATE_S)<37500))
GROUP BY tbl_G_stats_atp.PK_G, tbl_G_stats_atp.InjuryCnt;

It produces this result:

+---------+-----------+
|  PK_G   | InjuryCnt |
+---------+-----------+
| 1203857 |         0 |
| 1203881 |         0 |
| 1203890 |         0 |
| 1203913 |         0 |
| 1203916 |         0 |
| 1203989 |         0 |
| 1204001 |         0 |
| 1204102 |         0 |
| 1204172 |         0 |
+---------+-----------+

I want to select the last record so have used this SQL:

SELECT Last(tbl_G_stats_atp.PK_G) AS LastOfPK_G, tbl_G_stats_atp.InjuryCnt
FROM tbl_G_stats_atp
WHERE (((tbl_G_stats_atp.ID_A)=89) AND ((tbl_G_stats_atp.DATE_S)<37500))
GROUP BY tbl_G_stats_atp.InjuryCnt
ORDER BY Last(tbl_G_stats_atp.PK_G);

However it returns the first record (1203857).

I realise I can use this SQL as a replacement:

SELECT Max(tbl_G_stats_atp.PK_G) AS MaxOfPK_G, tbl_G_stats_atp.InjuryCnt
FROM tbl_G_stats_atp
WHERE (((tbl_G_stats_atp.ID_A)=89) AND ((tbl_G_stats_atp.DATE_S)<37500))
GROUP BY tbl_G_stats_atp.InjuryCnt;

However I'd like to understand why it's doing this. I may in future want to select the last record on a non-numeric field...

Upvotes: 0

Views: 45

Answers (1)

June7
June7

Reputation: 21370

Have to be careful with using First or Last because records do not have intrinsic order. Even with an ORDER BY clause, results can be not as expected. I have avoided Last/First but just did a simple test and was able to return value from last record added to table - no WHERE, GROUP BY, or ORDER BY clauses included.

If you want to return all fields from that record, consider:

SELECT TOP 1 tbl_G_stats_atp.* FROM tbl_G_stats_atp WHERE ID_A=89 AND DATE_S<37500 ORDER BY PK_G DESC;

Even then, there must be a field of unique values that can be relied on to order records so desired record is brought to top. Usually an autonumber ID is positive and increasing (I've never seen otherwise) and should accomplish that. Or perhaps a date/time field will serve.

Upvotes: 2

Related Questions