RE: impossible sql request

Hi, and thanks all. An explanation of the case : The 2 numerics are actually representations of date fields in the real table. I need to get all transactions where the date is the earliest in the whole table. The rule is, if the date in n2 is <>0, then that is the transaction date and n1 must therefore be ignored. If n2==0, then n1 is used.

Joe, I thought your first statement was right, although I haven't yet tried it.

-----Message d'origine-----
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Joe Pluta

In the case below, why is the row [c1, 4, 0] included?
Because it contains 4 in n1, or because it contains 0 in n2?

Because of the 4 in n1. N2 would not be used as it is 0.

Why don't you include [c1, 4, 5]? I don't understand the
phrase "should return 2 lines containing 4". There are 3
lines containing 4.

The value for this line would be 5 as n2 is used if it has a value <>0 and n1==4 is therefore ignored. As 5 is greater than the min value, the line should be ignored.

How about adding a few more lines to the test case and
explaining the result.

If you add [c1, 2, 0] what are the results?

Then this would be the only line extracted. The transaction date is 2 which is the lowest for the table.

If you add [c1, 2, 5] what are the results?

The transaction date is 5 so the row will be ignored.

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

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.