SQL Query with DISTINCT

From: Antonio 6sette <bottesini_at_libero.it>
Date: Fri, 13 Jan 2006 09:29:32 +0000 (UTC)
Message-ID: <dq7rts$b9a$2@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;}

Received on Fri Jan 13 2006 - 03:29:32 CST