Please, read my comment to the question first.
You can't remove your question if there exists one or more answer. Please, read
CP QA rules[
^].
There were interesting solutions for MS SQL server where
ROW_NUMBER
function was used. Unfortunately, MySQL does not provides
ROW_NUMBER
function, but there is a hope ;) - using variables.
Try this:
SELECT date,
(
CASE baname
WHEN @curType THEN @curRow := @curRow + 1
ELSE @curRow := 1 AND @curType := baname
END) +1 AS RowNo,
baname,
bal
FROM tmp t2, (SELECT @curRow := 0, @curType := '') t1
WHERE `date` = '2013-12-23'
ORDER BY baname
Above query returns:
| DATE | ROWNO | BANAME | BAL |
|---------------------------------|-------|--------|--------|
| December, 23 2013 00:00:00+0000 | 1 | hb | 6726 |
| December, 23 2013 00:00:00+0000 | 2 | hb | 8354.4 |
| December, 23 2013 00:00:00+0000 | 1 | ib | 10000 |
| December, 23 2013 00:00:00+0000 | 2 | ib | 9500 |
| December, 23 2013 00:00:00+0000 | 3 | ib | 9000 |
| December, 23 2013 00:00:00+0000 | 4 | ib | 9500 |
All what you need to do now, is to select
baname
with the highest
RowNo
. Tip: use
MAX()
function ;)
SQLFiddle example[
^]
[EDIT]
It should produce expected result set:
SELECT date, MAX(RowNo), baname, bal
FROM (
SELECT date,
(
CASE baname
WHEN @curType THEN @curRow := @curRow + 1
ELSE @curRow := 1 AND @curType := baname
END) +1 AS RowNo,
baname,
bal
FROM tmp t2, (SELECT @curRow := 0, @curType := '') t1
WHERE `date` = '2013-12-23'
ORDER BY baname
) T
GROUP BY T.baname
[/EDIT]