CODEWITHSUNDEEP
sqloracle-databaseselect
DARK_A
DARK_A

Reputation: 575

Remove last line in select

How do I remove last row from selected data. I need to sort a data and order it. But last row is never used. maybe someone knows an exemple?

Upvotes: 5

Views: 7160

Answers (4)

David Aldridge
David Aldridge

Reputation: 52396

Given that you have some query that returns a certain number of rows, you can use COUNT(*) OVER () to find out how many rows it is returning. To eliminate the final row you must push the query into a subquery and filter on the ROWNUM ...

select ...
from   (select   count(*) over () total_rows,
                 ...
        from     ...
        where    ...
        order by ...)
where   rownum < total_rows

Upvotes: 2

user1844626
user1844626

Reputation: 1868

see this problem if yours is similar MySQL: How to select all rows from a table EXCEPT the last one and see this code there

SELECT * FROM TABLE WHERE ID != (SELECT MAX(ID) FROM TABLE)

here "ID != (SELECT MAX(ID) FROM TABLE)" this part cuts the last row which have the highest number in serial(for this, you have to declare an integer field(ID) which will be auto_increment.that field will give you the largest value on the last row of the records by MAX(ID))

Upvotes: 1

hahn
hahn

Reputation: 3658

you can try this

with t as (select 1 field from dual
union all 
select 2 from dual)

select * from (
  select count(*) over(order by field desc) cnt, t.* from t
) 
where cnt != rownum

Upvotes: 6

Pranay Rana
Pranay Rana

Reputation: 176956

try like this 

select * from table
where rowid<>(select max(rowid) from table);

or 

select * from table
minus
select * from table where rownum = (select count(*) from table)

Upvotes: -1

Related Questions