I have PostgreSQL table:
Username1 SomeBytes1
Username2 SomeBytes1
Username1 SomeBytes1
Username1 SomeBytes1
I need to get some rows from with name Username1 but from the end of the table. For example i need last to rows with Username1
select from my_table where user = Username1 LIMIT 2
Gives me first 2 rows, but i need last two.
How can i select it?
Thank you.
first and last in a table is very arbitrary. In order to have a good predictable result you should always have an order by clause. And if you have that, then getting the last two rows will become easy.
For instance, if you have a primary key or something like an ID (which is populated by a sequence), then you can do:
select * from my_table where user = 'Username1' order by ID desc limit 2.
desc tells the database to sort the rows in reverse order, which means that last will be first.
Does your table have a primary key ? / Can your table be sorted? Because the notion of 'first' and 'last' implies some sorting of the tuples. If this is the case, you could sort the data the other way around, so that your 'last' entries are on top. Then you can access them with the statement you tried.
To view tail of a table you may use ctid. It is a temporary physical identifier of a record in PostgreSQL.
SELECT * from my_table
WHERE user = Username1
ORDER BY ctid DESC
LIMIT 2
