RE: Need SELECT tips

Okay, Olaf, I agree with you.
That's because it is unclear what the guy (Violin) did want to do.

Your assumption is that what he wants is to have the first key for a given value. Hence your group by.

We can also assume that it was only to have a value for 3 specific key values (A,C,E). But the answer is also obvious.

The only reason I could imagine someone was asking such a question, was that it was something non-SQLable...
Hence something I use sometimes when the place of the row in the table is of importance (that's true that I should have added an ORDER BY col1 clause in the inline view).

My example is able to retrieve odd key values (a kind of more powerful "ROWNUM") is the table (in other words, 1 row every 2 rows).

This a very useful technique which can be use to emulate some functions one can find in certain tools (especially for datawarehouse manipulations) like first(), last() which yield the first row found (resp the last).

It is even more tricky to make the use of such functions valid in a GROUP BY statement as they are not natively supported grouping functions ...

Frederic.

-----Original Message-----
From: Olaf Naumann [mailto:olaf_at_news2mail.com] Sent: 4. februar 2000 19:56
To: Frederic Bidon
Subject: Re: Need SELECT tips

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
>

oops, that looks a bit too complex for me:

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

would also produce the expected result.

olaf

>
> -----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

 Sent via Deja.com http://www.deja.com/  Before you buy. Received on Fri Feb 04 2000 - 13:05:03 CST