Re: impossible sql request

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

No, that's how I read it first, Steve, but I think David wants something 
a little more complex.  First, he wants to figure the "reference value", 
which is the lowest of either n1 where n2 is not zero, or n2.  In this 
dataset, that returns 4.  Once done, he then wants all rows where either 
n1 or n2 matches that result.

A CTE should handle this pretty easily.

with t1 as (
  select min(N1) as minVal from TABLE where N2 = 0
  union
  select min(N2) as minVal from TABLE),
t2 as select min(minVal) as refVal from t1
select * from TABLE
  where N1 in (select refVal from t2)
  or N2 in (select refVal from t2)

That's attempt number one.  Another way is to go against each row with a 
case to determine the reference value.  That select might look something 
like this:

select min(case
  when n2 > 0 then n2
  when n2 > n1 then n2
  else n1 end) as refVal from TABLE

Then do the select * from TABLE.


Joe


> select * from test a
>   where n1 = (select Min(n1) from test where a.id = id)
> or n2 = (select Min(n2) from test where a.id = id)
>
> not particularly pretty...but it works.
>
> steve
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
> Sent: Wednesday, February 09, 2011 7:55 AM
> To: Midrange Systems Technical Discussion
> Subject: impossible sql request
>
>
> Hi all,
> I need to scan this table and get the lines having the lowest value for n1 or n2
>
> id	n1	n2
> c1	4	0	
> c1	3	4	
> c1	4	5	
> c1	5	6	
>
> If n2 is not 0, I ignore the value of n1.
>
> So in this example I should return 2 lines containing 4 :
>
> c1	4	0
> c1	3	4
>
> Thanks