CODEWITHSUNDEEP
mysql
john BB
john BB

Reputation: 79

mysql query select just last record by time

I have table like this: table exchaneRate

╔════╦══════════╦══════════════╦═════╦══════╦══════════════════╗
║ id ║ officeID ║ currencyCode ║ buy ║ sell ║ startDateTime    ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 01 ║  off_1   ║       AA     ║  65 ║  75  ║ 2015═10═01 12:00 ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 02 ║  off_1   ║       BB     ║  64 ║  73  ║ 2015═10═01 12:00 ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 03 ║  off_1   ║       AA     ║  55 ║  65  ║ 2015═09═25 12:00 ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 04 ║  off_1   ║       BB     ║  54 ║  63  ║ 2015═09═25 12:00 ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 05 ║  off_1   ║       AA     ║  30 ║  42  ║ 2015═09═15 12:00 ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 06 ║  off_1   ║       BB     ║  40 ║  48  ║ 2015═09═15 12:00 ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 07 ║  off_2   ║       AA     ║  65 ║  75  ║ 2015═10═01 12:00 ║
╠════╬══════════╬══════════════╬═════╬══════╬══════════════════╣
║ 08 ║  off_2   ║       BB     ║  65 ║  75  ║ 2015═10═01 12:00 ║
╚════╩══════════╩══════════════╩═════╩══════╩══════════════════╝

And I have request like this to select data:

    select `currencyCode`, `buy`, `sell` from `exchangeRate` 
where `officeID` = 'off_1' and startDateTime <= '2015-09-30 00:00';

And I want this result:

╔══════════════╦═════╦══════╗
║ currencyCode ║ buy ║ sell ║
╠══════════════╬═════╬══════╣
║       AA     ║  55 ║  65  ║
╠══════════════╬═════╬══════╣
║       BB     ║  54 ║  63  ║
╚══════════════╩═════╩══════╝

But request returns all record AA, BB, AA, BB, AA, BB... from table. But I need just last records for each currencyCode for this officeID. How can I do that?

Upvotes: 1

Views: 43

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

I would go with the following approach. The inner query determines the records you want for each currencyCode using your time range. The original exchangeRate table is then filtered on this inner query through an INNER JOIN to give the output you want.

SELECT t1.currencyCode, t1.buy, t1.sell
FROM exchangeRate t1 INNER JOIN
(
    SELECT currencyCode, MAX(startDateTime) AS maxTime
    FROM exchangeRate
    WHERE startDateTime <= '2015-09-30 00:00' AND officeID = 'off_1'
    GROUP BY currencyCode
) t2
ON t1.currencyCode = t2.currencyCode AND t1.startDateTime = t2.maxTime

Click below for a running demo.

SQLFiddle

Upvotes: 0

Anik Islam Abhi
Anik Islam Abhi

Reputation: 25352

try like this 

select `currencyCode`, `buy`, `sell` from `exchangeRate` 
where id in 
( select max(id) from exchangeRate  
   where `officeID` = 'off_1' and startDateTime <= '2015-09-30 00:00' group by currencyCode 
)

SQLFIDDLE

Upvotes: 1

Arun Krish
Arun Krish

Reputation: 2153

Try like this

SELECT currencyCode,
 SUBSTRING_INDEX(GROUP_CONCAT(buy ORDER BY startDateTime DESC),',',1) as buy_1,
 SUBSTRING_INDEX(GROUP_CONCAT(sell ORDER BY startDateTime DESC),',',1) as sell_1,
 SUBSTRING_INDEX(GROUP_CONCAT(startDateTime ORDER BY startDateTime DESC),',',1) as date_1 
FROM exchangeRate
WHERE officeID = 'off_1'
GROUP BY currencyCode
HAVING date_1 <= '2015-09-30 00:00'
ORDER BY currencyCode

Upvotes: 0

Related Questions