SQL to retrieve last 'n' rows of table

[SonomaCharles]

I am using HSQLDB Embedded. I am an experienced coder but have scant SQL experience.

I have a table Client_History that includes a date column and a “route” column with a value {A, B, C, D, E, F}. Neither column is unique. (The primary key is an ID column.) I want to generate a report that has the 30 most recent entries.

SELECT * FROM "Client_History" ORDER BY "Date", "Route" ASC gives me the entire table sorted by date and route (of course). Appending LIMIT 30 gives me the first 30 rows. How do I instead get the last 30 rows? The Goo Gal informs me that some SQL implementations support SELECT TOP 30 ... but apparently not HSQLDB. How might I accomplish this?

I have a vague notion that one might sort descending, take the first 30, and then re-SELECT sorted ascending, but I have insufficient SQL skills to be able to code that.

The table is for the foreseeable future no more than a few hundred rows. Performance is not likely to be a problem, no matter how I do this.

[gregors15]

Try just changing your ASC (ascending) to DESC (descending). I have an example
SELECT “Supplier”, “Date”, “Total” FROM “SupplierT” ORDER BY “Date” DESC LIMIT 10, which shows me the latest 10 transactions.

Using LO 25.2.4.3, windows 10 Home.
Let us know if it helps.

[SonomaCharles]

I’m sorry. Yes, your solution solves what I asked. I left out part of the requirement: I want the report sorted by (Date, Route) ascending. I want the last 30 rows in ascending sequence.

[RobertG]

First create a view

SELECT * FROM "Client_History" ORDER BY "Date", "Route" DESC LIMIT 30

Then create a query

SELECT * FROM "MyView" ORDER BY "Date", "Route" ASC

Might be you need other limits. So you could create a table like “tbl_Filter” for only one row and read the value for the limit from this “tbl_Filter”.

[SonomaCharles]

Yes! Thank you. This makes sense.

I created the view exactly as you specified. But when I click on it it gives me the first 30 dates, sorted ascending.

Aha! Got it! I need ORDER BY "Date" DESC, "Route" DESC

This seems to work. More testing required but I think you solved it. Thanks!