CODEWITHSUNDEEP
mysqlmax
Jens Myretyr
Jens Myretyr

Reputation: 11

Multiple columns and sorting

I have tried for several hours now to get my SQL query to sort by price. Here we go:

Table INFO

id (unique id)
mid (some id)
name

Table PRICES

id (some unique id)
fuid (id for showing wich entry from INFO this price is connected to)
dated (date)

SQL Query:

select INFO.id, INFO.mid, INFO.name, PRICES.price, PRICES.dated
from INFO, PRICES
WHERE INFO.id = PRICES.fuid
ORDER BY PRICES.dated asc
limit 100

The result I want is a list of all entries in INFO with the latest price from PRICES. Only the latest price, one price per INFO entry.

What I get: I get a list of unique entries from INFO but I get the first price entered in the PRICES, not the latest.

I cant get max() to help me sort the prices column. Anybody who knows how to solve this?

Upvotes: 1

Views: 61

Answers (1)

kendsnyder
kendsnyder

Reputation: 783

Did you use GROUP BY with MAX()? The following use of MAX() and GROUP BY would probably do what you need:

select INFO.id, INFO.mid, INFO.name, PRICES.price, MAX(PRICES.dated) 
from INFO, PRICES 
where INFO.id = PRICES.fuid
group by INFO.id, INFO.mid, INFO.name, PRICES.price
order by INFO.name asc 
limit 100

Note that the ORDER BY column(s) can be anything you want; it doesn't have to be MAX(PRICES.dated).

EDIT: I see what you mean. You could use a temporary table like this SQL Fiddle.

Upvotes: 1

Related Questions