| alternative for min() function [message #221764] |
Wed, 28 February 2007 06:05  |
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  |
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 |
joy_division
Messages: 4963
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
