return last value in a sorted query / table

[markjkubicki]

i'm looking for the field value of the last record in a query / table with an SQL sorted order (I presume that this is differnet (?) than the MAX() value of the field, which may not be the value of the last record.)



is there a command line that will get this to me (or do I need to use a series opf steps: requery, go to last record, get data, and so forth...) ?

many thanks in advance,
mark

[jzwp11]

Welcome to the Forum!

If you are using a sequential autonumber primary key field, you can get the last record added using an aggregate query nested within another query


SELECT fieldofinterest
FROM yourtable
WHERE keyfield IN (SELECT Max(autonumberfield) from yourtable)

Alternatively, in you could create another query based on your sorted query and use the following syntax

SELECT Top 1 field1, field2...
FROM yoursortedquery

[markjkubicki]

I'm trying to get the data from the record which would be at the bottom of the sorted query / table; it may not be the last record entered.

for ex:
record # value
1 A
2 C
3 B

if the sort order is alphabetical / ascending, I would want the result of record #2, "C"
if the sort order was descending, the result would be record #1, "A"

I should also note that my sort order is not a "simple" (ex: alpha, numeric, ...), and requires a a SQL query...

[jzwp11]

Reverse the sort order of your sorting query such that the last record becomes the first record and then use the alternative option I proposed:

SELECT Top 1 field1, field2...
FROM yoursortedquery

SQL does not have a Bottom predicate so you have to reverse the order and use the Top predicate.

[markjkubicki]

Reverse the sort order of your sorting query such that the last record becomes the first record and then use the alternative option I proposed:

SELECT Top 1 field1, field2...
FROM yoursortedquery

SQL does not have a Bottom predicate so you have to reverse the order and use the Top predicate.

thnx for the tip

[jzwp11]

Let us know if what I proposed works for you. If not, there may be another approach, but we would have to get a better understanding of your data and your table structure.