Re: SQL Query with DISTINCT

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 13 Jan 2006 09:42:58 +0000 (UTC)
Message-ID: <dq7sn2$7jr$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>

"Antonio 6sette" <bottesini_at_libero.it> wrote in message news:dq7rts$b9a$2_at_area.cu.mi.it...
> Hi all,
>
> how can i "translate" this query from Postgresql SQL in Oracle SQL ??
>
> SELECT DISTINCT ON (rif_calcolo)
> descr,validita,imp,rif_calcolo
> FROM indennita WHERE societa=1 AND
> qualifica=3 AND validita<='1/1/2006' ORDER BY
> rif_calcolo,validita DESC;
>
> IND.TURNO | 01/01/2005 | 24.72000 | I1
> IND.GIOIA TAURO | 01/07/2003 | 351.19000 | I10
> ASS.AD PERSONAM COEFF. | 01/06/2004 | 231.14000 | I11
> ASS.AD PERSONAM NOCOEFF | 01/06/2004 | 248.86000 | I12
> HHHH | 01/01/2004 | 150.00000 | I13
> ASS.AD PERS. COEFF. | 01/01/2005 | 292.84000 | I14
> ASS.AD PERS. NO COEFF. | 01/01/2005 | 176.69000 | I15
> IND.SPECIALE | 01/06/2004 | 0.00000 | I2
> ELEM.MAGG. | 01/09/2003 | 226.21000 | I4
> IND.FUNZIONE | 01/01/2005 | 200.00000 | I5
> IND.SPECIALE OP. | 01/07/2003 | 135.31000 | I6
> IND.RAPPR. | 01/07/2003 | 44.42000 | I9
> (12 rows)
>
> the clause DISTINCT ON () permits me to obtain the most recent row with
> the
> same 'rif_calcolo'. In Oracle, distinct return the data of all the row,
> because the 'imp' is different.
>
> tanks in advance.
>
> --
> #include <stdio.h>
> int main(void){char
> c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,111,110,
> 101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
> 116,46,105,116,62,10,10,0};printf("%s",c);return 0;}

When you say it returns the most recent row, is this because you are using validita to identify the most recent row, and have an "order by desc" on that column ?

If so, then you need to look at Oracle's analytic functions to do the same sort of thing - something like

select

    *
from (

    select

        descr,validita,imp,rif_calcolo,
        row_number() over (
            partition by rif_calcolo order by validita desc
        ) row_num

    from indennita

    where societa=1
    and qualifica=3
    and validita<= to_date('1/1/2006','dd/mm/yyyy')

)
where row_num = 1
;

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 10th Jan 2006

Received on Fri Jan 13 2006 - 03:42:58 CST