Thread: Filtering a bunch of records to one after a selection has returned a bunch of them

Hi,

I am trying to establish whether or not it is possible - and how to
implement the solution - to execute an sql statement and restricting
the results to a single match even though there may be more than a
single record which matches the criteria.

e.g. select min(counter) from my_table butonlyone;

Cheers
Joe

Hello, Joe!

On Thu, Dec 27, 2001 at 07:01:16AM -0800, you wrote:

> I am trying to establish whether or not it is possible - and how to
> implement the solution - to execute an sql statement and restricting
> the results to a single match even though there may be more than a
> single record which matches the criteria.
>
> e.g. select min(counter) from my_table butonlyone;
select min(counter) from my_table limit 1;

--
NEVE-RIPE

> I am trying to establish whether or not it is possible - and how to
> implement the solution - to execute an sql statement and restricting
> the results to a single match even though there may be more than a
> single record which matches the criteria.
>
> e.g. select min(counter) from my_table butonlyone;

SELECT
CASE
WHEN min(my_table.counter) < min(butonlyone.counter) THEN
  min(my_table.counter)
ELSE
  min(butonlyone.counter)
END AS MinCounter
FROM my_table,butonlyone;

Alternately, you could write a function that takes a number of arguments
(depending on how many you might need... or even an array of values) and
returns the lowest one. That way you could do something like:

SELECT my_min(my_table.counter,butonlyone.counter) AS MinCounter FROM
my_table,butonlyone;

Greg

jfc100@btopenworld.com (Joe) wrote in message news:<88d9f4b3.0112270701.4a3741d@posting.google.com>...

> I am trying to establish whether or not it is possible - and how to
> implement the solution - to execute an sql statement and restricting
> the results to a single match even though there may be more than a
> single record which matches the criteria.
>
> e.g. select min(counter) from my_table butonlyone;

Ummm ... "select something from thistable limit 1".

Or is there something I'm missing here?

Calvin