× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.