CODEWITHSUNDEEP
mysqlsqldatabase
Dail
Dail

Reputation: 4608

Get the last row in MYSQL

I have this query:

SELECT 
    s.last_spread, s.sd, s.mean, s.id
    ,c.id_ticker, c.coef
    ,t.ticker
    ,/*p.last,*/ p.price

FROM (SELECT * FROM spreads WHERE spreads.id_check=1 LIMIT 60,896) as s 

    INNER JOIN coef as c 
        ON c.id_spread = s.id

    INNER JOIN tickers AS t
            ON t.id = c.id_ticker

    LEFT JOIN (SELECT prices.id_ticker, MAX(prices.date) as last, prices.price FROM prices GROUP BY prices.id_ticker) AS p
            ON p.id_ticker = t.id

ORDER BY s.id, c.id

the last JOIN doesn't return the correct value....I need to get the LAST PRICE of the tickers (so the last row of each ticker).

At the moment it returns the first price, how can I change the entire query to get the last prices of each ticker.id ? (p.id_ticker = t.id)

thanks!

Upvotes: 0

Views: 190

Answers (3)

Argeman
Argeman

Reputation: 1353

Using A having clause would be elegant:

LEFT JOIN (SELECT prices.id_ticker, prices.date as last, prices.price FROM prices GROUP BY prices.id_ticker HAVING prices.date = MAX(prices.date) )

Edit: But it does NOT work... (see comments below)

Upvotes: 2

newenglander
newenglander

Reputation: 2049

I would say use ORDER BY and a LIMIT to get only one price (the last price).

Upvotes: 1

juergen d
juergen d

Reputation: 204924

add a order by to this subquery:

SELECT prices.id_ticker, MAX(prices.date) as last, prices.price 
FROM prices GROUP BY prices.id_ticker 
order by prices.id_ticker desc

Upvotes: 3

Related Questions