CODEWITHSUNDEEP
sqloracle-database
EGN
EGN

Reputation: 2592

Select all but last row in Oracle SQL

I want to pull all rows except the last one in Oracle SQL

My database is like this

Prikey - Auto_increment
common - varchar
miles - int

So I want to sum all rows except the last row ordered by primary key grouped by common. That means for each distinct common, the miles will be summed (except for the last one)

Upvotes: 1

Views: 5586

Answers (3)

mega
mega

Reputation: 1

query to retrieve all the records in the table except first row and last row 

select * from table_name 
where primary_id_column not in 
(
  select top 1 * from table_name order by primary_id_column asc
) 
and
primary_id_column not in 
(
  select top 1 * from table_name order by primary_id_column desc
)

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

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

Upvotes: 5

user1807807
user1807807

Reputation:

Looks like I am a little too late but here is my contribution, similar to Ed Gibbs' first solution but instead of calculating the max id for each value in the table and then comparing I get it once using an inline view.

SELECT d1.prikey,
  d1.authnum 
FROM myTable d1,
  (SELECT MAX(prikey) prikey myTable FROM myTable) d2
WHERE d1.prikey != d2.prikey

At least I think this is more efficient if you want to go without the use of Analytics.

Upvotes: 0

Related Questions