Re: impossible sql request

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