Re: Need SELECT tips

oops, that looks a bit too complex for me:

select min(col1), col2 from temp group by col2;

would also give the expected result.

olaf

Frederic Bidon wrote:

> As far I understand :
>
> 1/ The easy way.
> select col1,col2 FROM TEMP where col1 IN ('A','C','E');
>
> That can be convenient if you don't have many relevant key values for col1.
>
> 2/ The nasty way.
> If what you want is actually to skip one line from the base table every 2
> lines and you have a high number or umpredicatable number of values, it is a
> bit more difficult :
>
> CREATE OR REPLACE PACKAGE myfunc IS
> FUNCTION isodd(col1 IN TEMP.col1%TYPE) RETURN INTEGER;
> PRAGMA RESTRICT_REFERENCES(isodd,WNDS);
> END myfunc;
>
> CREATE OR REPLACE PACKAGE BODY myfunc IS
> priv_state INTEGER;
> FUNCTION isodd(col1 IN TEMP.col1%TYPE) RETURN INTEGER IS
> BEGIN
> IF priv_state = 0 THEN
> priv_state:=1;
> RETURN 1;
> ELSE
> priv_state:=0;
> RETURN 0;
> END IF;
> END isodd;
>
> BEGIN
> priv_state:=0;
> END myfunc;
>
> select t1.col1,t1.col2 FROM
> (select myfunc.isodd(col1) num,col1,col2 FROM TEMP) t1
> WHERE t1.num = 1;
>
> You can of course imagine any kind of hash-like function which can yield a
> convenient code from you key value.
>
> Enjoy !
>
> Frederic
>
> -----Original Message-----
> From: Violin [mailto:violin.hsiao_at_mail.pouchen.com.tw]
> Sent: 28. januar 2000 06:19
> To: comp.databases.oracle.server_at_list.deja.com
> Subject: Need SELECT tips
>
> Message from the Deja.com forum:
> comp.databases.oracle.server
> Your subscription is set to individual email delivery
>
> Hello,
> If I have a table : TEMP
> COL1 CHAR(1) primary key
> COL2 CHAR(1)
>
> col1 col2
> ---------------
> A 1
> B 1
> C 2
> D 2
> E 3
> F 3
>
> How could I select the result?
>
> col1 col2
> ---------------
> A 1
> C 2
> E 3
>
> Thank you for any tips.
>
> Violin.
> violin.hsiao_at_mail.pouchen.com.tw
>
> _____________________________________________________________
> Deja.com: Before you buy.
> http://www.deja.com/
> * To modify or remove your subscription, go to
> http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
> * Read this thread at
> http://www.deja.com/thread/%3C3893f0c5.236359%40172.16.7.5%3E
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Feb 04 2000 - 13:43:41 CST