Home » SQL & PL/SQL » SQL & PL/SQL » alternative for min() function
alternative for min() function [message #221764] Wed, 28 February 2007 06:05 Go to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi All,

Please provide ur suggestions reg the below problem:

is there any other alternative for using min() function???
i need to use this function on a column in a table which has lakhs of records (almost nearer to 1 crore).
Re: alternative for min() function [message #221775 is a reply to message #221764] Wed, 28 February 2007 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the column is indexed, you can do:
SELECT column_name
FROM (SELECT column_name
      FROM  table
      ORDER BY column_name)
WHERE rownum=1


However, the tests I've just done suggest that it's going to be an order of magnitude slower than doing a straightforwards
SELECT min(column_name)
FROM   table.


If I remember correctly, a Lakh is 100,000 and a crore is 10,000,000?
That's not a very large number of rows.

What exactly is the problem you are having?
Re: alternative for min() function [message #221793 is a reply to message #221764] Wed, 28 February 2007 08:00 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
This begs to ask once again, why would you want to rewrite an already built-in function that Oracle provides. Why, why why? Please give one good reason, any reason. I'll even accept a bad reason, but I'd really like to know why you want this?

It's like someone asking you to find another way to open can of vegetables instead of using a can opener, even though you have one in your possession.
Re: alternative for min() function [message #221869 is a reply to message #221764] Wed, 28 February 2007 13:36 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yeah, min will use an index if it can.

As far as "why do it the hard way, or another way", one possible reason is that often learning assignments are given that ask for this sort of thing. It is more of a learning or thought exercise.
Previous Topic: 10G Migration (Code Changes)
Next Topic: display elapsed time within PL/SQL
Goto Forum:
  


Current Time: Sat Dec 03 04:19:36 CST 2016

Total time taken to generate the page: 0.12225 seconds