How to get the last row of an Oracle table
There is no such thing as the "last" row in a table, as an Oracle table has no concept of order.
However, assuming that you wanted to find the last inserted primary key and that this primary key is an incrementing number, you could do something like this:
select *
from ( select a.*, max(pk) over () as max_pk
from my_table a
)
where pk = max_pk
If you have the date that each row was created this would become, if the column is named created
:
select *
from ( select a.*, max(created) over () as max_created
from my_table a
)
where created = max_created
Alternatively, you can use an aggregate query, for example:
select *
from my_table
where pk = ( select max(pk) from my_table )
Here's a little SQL Fiddle to demonstrate.
Select last row of table, Oracle 12c
You would seem to want this:
select t.*
from (select symbol
from ticker
order by price DESC
) t
where rownum <= 1;
Or in more modern versions of Oracle:
select symbol
from ticker
order by price desc
fetch first 1 row only;
Or, if you want to have fun:
select max(symbol) keep (dense_rank first order by price desc)
from ticket;
The where
condition goes before the order by
.
Get last row from a query generated table
You can wrap your query and use ROW_NUM()
:
SELECT *
FROM (
-- your query with an ORDER BY clause
) x WHERE ROWNUM = 1
Please note that this selects the first row in the resultset. If you want the last row, you would need to change your ORDER BY
clause to sort the opposite way in the first place.
If you are running Oracle 12c or higher, you don't need to wrap the query, you can use a FETCH FIRST ROW ONLY
clause.
SELECT ...
FROM ...
ORDER BY ...
FETCH FIRST ROW ONLY
Get last row per group in SQL
You can get the expected result with the following query:
select * from tbl1
where id1 in (select max(id1) from tbl1 group by desc1);
Select all but last row in Oracle SQL
Note: the question was changed after this answer was posted. The first two queries work for the original question. The last query (in the addendum) works for the updated question.
This should do the trick, though it will be a bit slow for larger tables:
SELECT prikey, authnum FROM myTable
WHERE prikey <> (SELECT MAX(prikey) FROM myTable)
ORDER BY prikey
This query is longer but for a large table it should faster. I'll leave it to you to decide:
SELECT * FROM (
SELECT
prikey,
authnum,
ROW_NUMBER() OVER (ORDER BY prikey DESC) AS RowRank
FROM myTable)
WHERE RowRank <> 1
ORDER BY prikey
Addendum There was an update to the question; here's the updated answer.
SELECT
common,
SUM(miles)
FROM (
SELECT
common,
miles,
ROW_NUMBER() OVER (PARTITION BY common ORDER BY prikey DESC) AS RowRank
FROM myTable
)
WHERE RowRank <> 1
GROUP BY common
Related Topics
Creating Temporary Tables in SQL
Mysql: What Is a Reverse Version of Like
SQL Server Select Distinct Rows Using Most Recent Value Only
Sql: Select Dynamic Column Name Based on Variable
The Parameterized Query Expects the Parameter Which Was Not Supplied
How to Group on Continuous Ranges
Splitting the String in SQL Server
Insert an Image in Postgresql Database
MySQL Equivalent of Decode Function in Oracle
How to Get Oracle Create Table Statement in SQL*Plus
Get the Default Values of Table Columns in Postgres
MySQL Remove Duplicates from Big Database Quick
SQL How to Compare Two Tables for Same Data Content
What Is a 'Multi-Part Identifier' and Why Can't It Be Bound
Does Ms Access Support "Case When" Clause If Connect with Odbc