2

I have a little problem with my query, here is my table:

CREATE TABLE IF NOT EXISTS `realizado` (
`cod` int(11) NOT NULL AUTO_INCREMENT,
`datedoc` date NOT NULL,
`bank` int(11) NOT NULL,
`bankValue` float NOT NULL,
PRIMARY KEY (`cod`));

INSERT INTO `realizado` (`cod`, `datedoc`, `bank`, `bankValue`) VALUES
(152, '2014-10-22', 22, 1000),
(153, '2014-10-22', 23, 2000),
(154, '2014-10-22', 24, 3000),
(200, '2014-10-23', 22, 950),
(201, '2014-10-25', 22, 100),
(202, '2014-10-25', 23, 2050),
(203, '2014-10-24', 22, 150),
(204, '2014-10-24', 24, 3800);

The problem is: I need to get the bankValue from a date and still group by bank, something like this: 

SELECT bank, bankValue
FROM realizado
WHERE datedoc <= '2014/10/25'
GROUP BY bank

the closest I got is: 

SELECT r.bank, (select bankValue from realizado r2 where max(r.cod) = r2.cod) as Value
FROM realizado as r
WHERE r.datedoc <= '2014/10/25'
GROUP BY r.bank

here's the SQL Fiddle if u like -> http://sqlfiddle.com/#!2/83e309/2

the result that I expect is ( 22 - 100 / 23 - 2050 / 24 - 3800 )

Fernando Apolinário
  • 23
  • 2
  • sorry about that, I searched for a week about it but I didn't found nothing like my problem, now looking that question "Retrieving the last record in each group" I realised that's it I should had searched – Fernando Apolinário Oct 25 '14 at 02:47

2 Answers2

1

You seem to want the latest value for the bank. If so, you can do:

select r.*
from realizado r
where not exists (select 1 from realizao r2 where r2.bank = r.bank and r2.datedoc > r.datedoc);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Here you go! (Thanks for setting up the sqlFiddle with DDL and bootstrap data :)

Working sqlFiddle: http://sqlfiddle.com/#!2/83e309/10

SELECT  a.*
FROM    realizado a
        INNER JOIN
        (
            SELECT  bank, MAX(datedoc) datedoc
            FROM    realizado
            GROUP   BY bank
        ) b ON a.bank = b.bank AND
                a.datedoc = b.datedoc
Community
  • 1
  • 1
Shiva
  • 20,575
  • 14
  • 82
  • 112