On Wed, Aug 17, 2005 at 11:57:52 -0700,
Ron Mayer <[EMAIL PROTECTED]> wrote:
> Richard Huxton wrote:
> >
> >While the other answers all do their job, and in one go too, I'd be
> >surprised if you found anything faster than:
> >
> >SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT
Richard Huxton wrote:
> Poul Møller Hansen wrote:
>
>> Does anyone know how to find the row with the nearest numeric value,
>> not necessarily an exact match ?
>
>
> While the other answers all do their job, and in one go too, I'd be
> surprised if you found anything faster than:
>
> SELECT myv
Richard Huxton wrote:
While the other answers all do their job, and in one go too, I'd be
surprised if you found anything faster than:
SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1
Really? Aren't most things with ORDER BY O(n*log(n))?
Or is the optimizer smart enou
To find the nearest value in number_column to some CONSTANT (where you
replace constant with a number), try:
select *,(number_column - CONSTANT)^2 as d from tablename order by d limit
1;
Does that do it for you?
Sean
It does ideed, not that I understood how, but I will find out.
Thank you v
Csaba Nagy <[EMAIL PROTECTED]> writes:
> The only problem is that you can't use the order by/limit syntax inside
> the union queries I guess, cause the query you proposed is giving a
> syntax error.
Parentheses are your friend ;-)
regards, tom lane
---
On Wed, Aug 17, 2005 at 17:35:37 +0200,
Csaba Nagy <[EMAIL PROTECTED]> wrote:
> The only problem is that you can't use the order by/limit syntax inside
> the union queries I guess, cause the query you proposed is giving a
> syntax error. I also thought first to do it like this, but it won't
> wor
Yep, you're right. The following works and uses the index on pk_col:
prepare test_01 (bigint) as
select * from
(
(SELECT * FROM big_table WHERE pk_col > $1 ORDER BY pk_col LIMIT 1)
UNION ALL
(SELECT * FROM big_table WHERE pk_col < $1 ORDER BY pk_col DESC
LIMIT 1)
) as nearest
order
The only problem is that you can't use the order by/limit syntax inside
the union queries I guess, cause the query you proposed is giving a
syntax error. I also thought first to do it like this, but it won't
work. If it would, then you could wrap the thing in another query which
orders by the diffe
On 8/17/05 10:01 AM, "Poul Møller Hansen" <[EMAIL PROTECTED]> wrote:
>
>> To find the nearest value in number_column to some CONSTANT (where you
>> replace constant with a number), try:
>>
>> select *,(number_column - CONSTANT)^2 as d from tablename order by d limit
>> 1;
>>
>> Does that do it
Poul Møller Hansen wrote:
Does anyone know how to find the row with the nearest numeric value, not
necessarily an exact match ?
While the other answers all do their job, and in one go too, I'd be
surprised if you found anything faster than:
SELECT myval FROM mytable WHERE myval > 1234 ORDER
Sean Davis wrote:
> On 8/17/05 8:50 AM, "Poul Møller Hansen" <[EMAIL PROTECTED]> wrote:
>
>
>>Does anyone know how to find the row with the nearest numeric value, not
>>necessarily an exact match ?
>
>
> To find the nearest value in number_column to some CONSTANT (where you
> replace constant w
[snip]
> To find the nearest value in number_column to some CONSTANT (where you
> replace constant with a number), try:
>
> select *,(number_column - CONSTANT)^2 as d from tablename order by d limit
> 1;
>
This will scan the whole table and sort the results... and then pick
just one of it. Watch
To find the nearest value in number_column to some CONSTANT (where you
replace constant with a number), try:
select *,(number_column - CONSTANT)^2 as d from tablename order by d limit
1;
Does that do it for you?
Sean
It does ideed, not that I understood how, but I will find out.
Thank you
On 8/17/05 8:50 AM, "Poul Møller Hansen" <[EMAIL PROTECTED]> wrote:
> Does anyone know how to find the row with the nearest numeric value, not
> necessarily an exact match ?
To find the nearest value in number_column to some CONSTANT (where you
replace constant with a number), try:
select *,(num
Does anyone know how to find the row with the nearest numeric value, not
necessarily an exact match ?
Thanks,
Poul
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EM
15 matches
Mail list logo
