CODEWITHSUNDEEP
mysql
user3063952
user3063952

Reputation: 97

MySQL Selecting the row with the greatest value in a field

I'm making a sales component for my software, the system allows to pay a product in more than one payment. So, if I introduce a selling code, the system will show the name of the person, the product, how much they paid and the number of the payment.

I have this query:

select cliente.Ape_Pat, 
    cliente.Ape_Mat, 
    cliente.Nom, 
    venta.Total, 
    venta.Pagado, 
    concat(evento.Nombre, ' ' ,evento.Fecha), 
    venta.ndp 
from venta, cliente, evento 
where venta.folio = '123456' 
  and venta.Id_cliente = cliente.Id_cliente 
  and venta.Id_evento = evento.Id_evento;

The problem is that this query shows ALL the results

Ape_Pat  Ape_Mat Nom      Total  Pagado      Concat            NDP 
Torres   Cuevas  Gustavo | 2376 | 2370 | Lamp July 2nd  |   1
Torres   Cuevas  Gustavo | 2376 | 2371 | Lamp July 3rd  |   2
Torres   Cuevas  Gustavo | 2376 | 2372 | Lamp July 4th  |   3

NDP is the number of the last payment, how can I fetch ONLY the row with the highest NDP value?

Upvotes: 0

Views: 61

Answers (3)

Hackerman
Hackerman

Reputation: 12295

Try this:

 select cliente.Ape_Pat, cliente.Ape_Mat, cliente.Nom, venta.Total, venta.Pagado,
 concat(evento.Nombre, ' ' ,evento.Fecha), venta.ndp from venta, cliente, 
 evento where venta.folio = '123456' 
 and venta.Id_cliente = cliente.Id_cliente 
 and venta.Id_evento = evento.Id_evento
 and venta.npd = (select max(npd) from venta where venta.folio = '123456');

Upvotes: 1

Reger
Reger

Reputation: 474

Using ORDER DESC and LIMIT: You should add at the end of your query:

ORDER BY NDP DESC 
LIMIT 0 , 1

Upvotes: 3

Spechal
Spechal

Reputation: 2716

Add an ORDER BY and LIMIT clause. i.e. SELECT ... FROM ... WHERE ... ORDER BY venta.NDP DESC LIMIT 0,1

Upvotes: 0

Related Questions