Max with many fields 1

[zrzr]

Hi !!!

I have a table names Devis, with many fields(but to resolve my problem, I only need to understand with 3 fields):

______________
Devis Numero | Indice | Montant
______________ _______________________________
Numero 3000 | | 5000
Indice 3001 | | 4700
Montant 3001 | A| 4900

What I need is to obtain only the rows where the indice is the higher for the numero. In my example, it would be :

3000 5000
3001 A 4900

With only Numero and indice, I can use
Select Numero, Max(Indice)
From Devis
Group By Numero

But if I add one column (Montant), I get an error.

Do you know any solution for my problem ??

 

[DanChard]

Give this a go:

select devis.* from devis,
(select Numero, nz(Max(Indice),' ') as indice1
from Devis
group By Numero) as temp1
where devis.numero = temp1.numero
and nz(devis.indice,' ') = temp1.indice1;

 

[zrzr]

Yeah !!!

It does exactly what I needed, and it's so ... short !

After posting my question, I did find a solution, using 2 queries, relations, joins, unions and so on.
But I think I will use your solution

Thanks a lot