Re: Query question
Bickel wrote:
> I have a table with 2 cols numid, subid both int
> numid subid
> 1 2
> 2 1
> 3 1
> 1 3
> 2 4
> 3 8
> 1 5
> 2 5
> 3 9
>
> Now i like to make a query in dbaccess wich would give my this result:
> 1 <--numid
> 2 <\\
> 3 < --all the subid's belonging to numid 1
> 5 </
> 2 <--numid
> 1 <\\
> 4 < --all the subid's belonging to numid 2
> 5 </
> 3 <--numid
> 1 <\\
> 8 < --all the subid's belonging to numid 3
> 9 </
>
> I tried it with union, i tried it with the set datatype via an temp
> table, but i can't get it working.
> Any ideas?
SELECT DISTINCT numid AS numid, numid AS subid FROM table
UNION
SELECT DISTINCT numid AS numid, subid AS subid FROM table
INTO TEMP t;
SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!
Everywhere else, you'd have to select numid as well as subid and
ignore the value.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<7NEsb.11218> >
>
> SELECT DISTINCT numid AS numid, numid AS subid FROM table
> UNION
> SELECT DISTINCT numid AS numid, subid AS subid FROM table
> INTO TEMP t;>
> SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!>
> Everywhere else, you'd have to select numid as well as subid and
> ignore the value.
Sorry, but this doesn't work. i get a result of only 11 rows where i
expect 12. Also on 9.40.
As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
Also ignoring the numid (or working with 9.40) are no options.
So i guess it can only be done with spl..?
numid subid
1 1
1 3
1 5
2 1
2 2
2 4
2 5
3 1
3 3
3 8
3 9
Bickel wrote:
> Jonathan Leffler <jleffler@earthlink.net> wrote:
>>SELECT DISTINCT numid AS numid, numid AS subid FROM table
>>UNION
>>SELECT DISTINCT numid AS numid, subid AS subid FROM table
>>INTO TEMP t;>>
>>SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!>>
>>Everywhere else, you'd have to select numid as well as subid and
>>ignore the value.
>
>
> Sorry, but this doesn't work. i get a result of only 11 rows where i
> expect 12. Also on 9.40.
> As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
> Also ignoring the numid (or working with 9.40) are no options.
> So i guess it can only be done with spl..?
> numid subid
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 3 8
> 3 9
OK - I see what I missed. I dunno whether it's worth rescuing what I
suggested, but you can add another column to be ignored except in the
sort phase:
SELECT DISTINCT numid AS numid, numid AS subid, 1 AS key FROM table
UNION
SELECT DISTINCT numid AS numid, subid AS subid, 2 AS key FROM table
INTO TEMP t;
SELECT subid FROM t ORDER BY numid, key, subid; -- IDS 9.40 only!
That makes sure that the 'key' columns for the numid appear before the
other values. I think that answers your query.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/