Home » SQL & PL/SQL » SQL & PL/SQL » select last value without analytic function
select last value without analytic function [message #436024] Mon, 21 December 2009 03:00 Go to next message
joshua82
Messages: 31
Registered: December 2009
Member
Hi!
I need to extract last value from a column in a query, without using analytic function (don't tell me why.. Laughing )

thanks
Re: select last value without analytic function [message #436026 is a reply to message #436024] Mon, 21 December 2009 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "last". ./fa/1987/0/

Regards
Michel
Re: select last value without analytic function [message #436027 is a reply to message #436026] Mon, 21 December 2009 03:03 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Michel Cadot wrote on Mon, 21 December 2009 03:02
Define "last".

..as a function?
Re: select last value without analytic function [message #436030 is a reply to message #436024] Mon, 21 December 2009 03:05 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

last value from a column in a query



  1* select * from (select employee_id from employees order by employee_id desc) where rownum=1
hr@hr> /

EMPLOYEE_ID
-----------
        206

Elapsed: 00:00:00.00

[Updated on: Mon, 21 December 2009 03:07]

Report message to a moderator

Re: select last value without analytic function [message #436036 is a reply to message #436030] Mon, 21 December 2009 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why "last" should mean MAX?
Please wait for OP's answer before posting inappropriate one.

Regards
Michel
Re: select last value without analytic function [message #436037 is a reply to message #436027] Mon, 21 December 2009 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joshua82 wrote on Mon, 21 December 2009 10:03
..as a function?

What does that mean?
And what does "last" mean for you? Isn't my question clear?

Regards
Michel

[Updated on: Mon, 21 December 2009 03:32]

Report message to a moderator

Re: select last value without analytic function [message #436040 is a reply to message #436030] Mon, 21 December 2009 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This:
select * from (select employee_id from employees order by employee_id desc) where rownum=1
is a more complex way of writing this:
SELECT max(employee_id) 
FROM   employees;


If some total wingnut has come up with a requirement that you avoid analytics, then you can use the FIRST / LAST aggregate functions:
create table test_117 (value_col number, sort_col  number, group_col  number);

insert into test_117 values (3,1,1);
insert into test_117 values (7,2,1);
insert into test_117 values (4,3,1);
insert into test_117 values (1,6,2);
insert into test_117 values (9,7,2);
insert into test_117 values (5,8,2);

select group_col,max(value_col) keep (dense_rank last order by sort_col)
from   test_117
group by group_col;
Re: select last value without analytic function [message #436041 is a reply to message #436037] Mon, 21 December 2009 03:20 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Michel Cadot wrote on Mon, 21 December 2009 03:15

What does that mean?
And what does "last" mean for you? Isn't my question clear?

sorry Michel, I misunderstand your question as an answer..
for last I mean:

VALUE
-----
101
103
105
108
109
102

last = '102'

[Updated on: Mon, 21 December 2009 03:20]

Report message to a moderator

Re: select last value without analytic function [message #436044 is a reply to message #436041] Mon, 21 December 2009 03:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Unless you've got another column to order the Value column by, then there is no meaning to 'last' in the context of a database.

Rows in a database are unordered - without an Order By clause, Oracle will not guarantee that rows will be returned in any particular order.
Re: select last value without analytic function [message #436045 is a reply to message #436044] Mon, 21 December 2009 03:30 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
JRowbottom wrote on Mon, 21 December 2009 03:25
Unless you've got another column to order the Value column by, then there is no meaning to 'last' in the context of a database.

Rows in a database are unordered - without an Order By clause, Oracle will not guarantee that rows will be returned in any particular order.

..it's true. I've another column 'date' (that's perfect for an order by).
I'm tring your solution.
Re: select last value without analytic function [message #436079 is a reply to message #436040] Mon, 21 December 2009 06:02 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

This:
select * from (select employee_id from employees order by employee_id desc) where rownum=1i 
is a more complex way of writing this:
 SELECT max(employee_id) 
FROM   employees;



I agree.

Seeing the OP's requirement
Quote:

last value from a column in a query

I wrote that way. Mis understood the question.
Tried to fetch the last value from a sub query..

Thanks!
icon7.gif  Re: select last value without analytic function [message #436673 is a reply to message #436079] Fri, 25 December 2009 02:44 Go to previous message
anan208
Messages: 1
Registered: December 2009
Location: 中国 浙江 杭州
Junior Member

you can add a sequence field to solution it.
Previous Topic: compare TWO CLOB column in two table (merged)
Next Topic: null data
Goto Forum:
  


Current Time: Sat Dec 03 14:11:26 CST 2016

Total time taken to generate the page: 0.19526 seconds