CODEWITHSUNDEEP
mysqlsqlselectmin
knrd
knrd

Reputation: 13

Select MIN() in SQL

I already wrote this sql statement to get person id, name and price for e.g. plane ticket.

SELECT Person.PID, Person.Name, Preis
FROM table.flug Flug
INNER JOIN table.flughafen Flughafen ON zielflughafen = FHID
INNER JOIN table.bucht Buchungen ON Flug.FID = Buchungen.FID
INNER JOIN table.person Person ON Buchungen.PID = Person.PID
WHERE Flug.FID = '10' ORDER BY Preis ASC;

My output is correct, but it should only be the line with min(Preis).

If I change my code accordingly, I get an error...

SELECT Person.PID, Person.Name, min(Preis)
FROM table.flug Flug ...

As output I need one single line: PID, Name and Price whereas Price is the min(Preis).

Upvotes: 0

Views: 156

Answers (3)

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

You need to group your result by Person.PID and Person.Name in order to select these fields in the same query where you're using aggregate function min().

SELECT Person.PID, Person.Name, min(Preis) as Preis
FROM table.flug Flug ....
WHERE Flug.FID = '10'
GROUP BY Person.PID, Person.Name
ORDER BY 3 ASC;

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

If the query is returning the result you want correctly, but you are getting more rows than you want, you can add a LIMIT 1 clause following the ORDER BY.

If you want to get just a single line for each unique combination of Person.PID and Person.Name, then add a GROUP BY clause before the ORDER BY clause, and use MIN() aggregate around Preis in the SELECT list.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311348

Since you're already sorting your lines, just add a limit clause:

SELECT     Person.PID, Person.Name, Preis
FROM       table.flug Flug
INNER JOIN table.flughafen Flughafen ON zielflughafen = FHID
INNER JOIN table.bucht Buchungen ON Flug.FID = Buchungen.FID
INNER JOIN table.person Person ON Buchungen.PID = Person.PID
WHERE      Flug.FID = '10' 
ORDER BY   Preis ASC
LIMIT      1

Upvotes: 0

Related Questions