Re: Problem with SQL statement

Tan Keok Tay wrote:
>
> Hello,
> I have the following problem.
> Imagine the data as follow.
>
> Field1 Field2 Field3
> ----- ----- -----
> P1 P2 P3
> P2 P3 P6
> P3 P1 P2
> P7 P2 P1
> P1 P3 P2
> P2 P7 P3
> P1 P2 P3
> p3 P4 P6
> P8 P3 P1
> :
> : (1000 records)
>
> I would like to select a certain number say 200 out of
> some many with the following order :
> P3 , P2 , P1 .
>
> ie .
>
> P3 P2 P1
> -- -- --
> P3 XX XX
> p3 XX XX
> XX P2 XX
> XX P2 XX
> XX P2 XX
> XX XX P1
> XX XX P1
> XX XX P1
> XX XX P1 , where XX= can be anything
>
> Any Body has a simple query to do this ??
> Have tried using UNION, but it doesn't work .

You have not specified how to handle a row such as   P3 P2 P1

Should this appear in each grouping or only once and, if so, in which group? If a qualifying row should appear in each group this will work:

select distinct field1 p3, field2 p2, field3 p1 from table_of_Ps
where field1 = 'P3'
union all
select distinct field1, field2, field3
from table_of_Ps
where field2 = 'P2'
union all
select distinct field1, field2, field3
from table_of_Ps
where field3 = 'P1'

-- 
Tomm Carr  --> Note new e-mail address: tommcatt_at_computer.org
---- ----  http://www.geocities.com/athens/delphi/6769

The Macintosh computer is like the open range;
In a world without fences, there are no Gates.