Requete et maximum

Bonjour,

j'ai une requete assez simple :

Citation:

---

select dim_value,address_type,sequence_no,address_id from agladdress where dim_value='1525'
and address_type='1'

---

qui renvoi:

Citation:

---

1525 1 0 26790920
1525 1 1 27795278

---

Comment faire pour que cette requete ne me renvoie que la ligne qui à le plus grand sequence_no (3ieme colonne).

Merci de votre aide.

Bonsoir,

j'ai reussi à trouver quelques choses

Citation:

---

select agl.dim_value,agl.address_type,agl.sequence_no,agl.address_id
from agladdress agl
INNER JOIN (select max(address_id) as idAdresse,address_type,dim_value,attribute_id from agladdress group by address_type,dim_value,attribute_id ) adresse ON agl.dim_value=adresse.dim_value and adresse.address_type='1' and adresse.attribute_id='JC02'
where agl.dim_value='1525'
and agl.address_id = adresse.idAdresse

---

mais je ne suis pas certain de l'optimisation et il y a peut être mieux

Vous pouvez le faire avec les fonctions FIRST / LAST :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

With agladdress AS ( select '1525' as dim_value, '1' as address_type, 0 as sequence_no, 26790920 as address_id from dual union all select '1525' , '1' , 1 , 27795278 from dual ) select dim_value, address_type, max(sequence_no) as sequence_no, max(address_id) keep(dense_rank last order by sequence_no asc) as address_id from agladdress where dim_value = '1525' and address_type = '1' group by dim_value, address_type;   DIM_VALUE ADDRESS_TYPE SEQUENCE_NO ADDRESS_ID 1525 1 1 27795278

---

je vais tester cela

cela fonctionne