I need a query in mysql

Question

1.18/5 (3 votes)

See more:

I am having a table ba

date          baname        bal
2013-12-23    ib             10000
2013-12-23    ib             9500
2013-12-23    ib             9000
2013-12-23    hb             6726
2013-12-23    hb             8354.40
2013-12-23    ib             9500

Now i need to get the value of last bal for each baname
i.e., Output is like

date          baname        bal
2013-12-23    hb            8354.40
2013-12-23    ib            9500

Suggest a solution

Posted 4-Jan-14 3:51am

sivamzcet

Updated 4-Jan-14 3:56am

Maciej Los

v3

Add a Solution

Comments

Maciej Los 4-Jan-14 9:56am

Hey! Do not remove your questions, if there are answers!!!
Rather than removing, improve question.
Respect CP QA rules!

sivamzcet 4-Jan-14 10:03am

i was not removed but some members closed my question. I dont know why?
Suggest me a correct solution

CPallini 4-Jan-14 12:37pm

In my opinion your problem is ambiguous, since there is not an exact order in your requirement.

3 solutions

Solution 1

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:

SQL

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:

SQL

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]

Posted 4-Jan-14 4:13am

Maciej Los

Updated 4-Jan-14 6:27am

v3

Comments

sivamzcet 4-Jan-14 10:44am

nice query :)
But i need only that two values as output. I cant get when i put Max(baname)

Maciej Los 4-Jan-14 10:57am

Not MAX(baname), but MAX(RowNo) ;)

sivamzcet 4-Jan-14 11:00am

SELECT baname,
(
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,MAX(RowNo)

is this right?

Maciej Los 4-Jan-14 11:04am

No ;(

sivamzcet 4-Jan-14 11:08am

Ouch!

sivamzcet 4-Jan-14 11:10am

its simple but i totally stucked

Maciej Los 4-Jan-14 11:19am

See the documentation: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max

sivamzcet 4-Jan-14 11:28am

SELECT date,MAX(RowNo),
(
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

sivamzcet 4-Jan-14 12:02pm

are you there?

Maciej Los 4-Jan-14 12:27pm

Yes, see updated answer.

sivamzcet 4-Jan-14 13:16pm

hiii maciej
the value of bal getting is wrong.
i get like this http://sqlfiddle.com/#!2/9602a/6
but its wrong coz i need to get 8354.4 and 9500

Maciej Los 4-Jan-14 13:19pm

Test it on real data, not on SQLFiddle site. There are known bugs...

sivamzcet 4-Jan-14 13:19pm

RowNo 1 value was displayed in bal for both baname
this is not the required output.
give a solution i hope u do :)

Maciej Los 4-Jan-14 13:21pm

I can't test it on real data, because i did not have installed MySQL server.
The query is good, technically...

sivamzcet 4-Jan-14 13:32pm

yeh good query but the value of bal we getting is wrong.

Solution 2

HI Siva,

this may not be the efficient solution.
but this will gives you the expected result..

SQL

declare @tbloutput table ( datee varchar(20), baname varchar(15) , bal int , ignore int )
declare @baname varchar(15)
declare cur cursor for select distinct baname from test open cur
fetch next from cur into @baname
while @@FETCH_STATUS =0
begin
 insert into @tbloutput select top 1  datee, baname, bal,  ROW_NUMBER() over ( order by datee) as Number  from test where baname = @baname  order by Number desc
fetch next from cur into @baname
end
close cur
deallocate cur
select datee,baname,bal from @tbloutput

Posted 4-Jan-14 8:34am

Karthik_Mahalingam

Comments

sivamzcet 4-Jan-14 22:52pm

thanks for your query!! but i cant able to get correct ans

Karthik_Mahalingam 4-Jan-14 23:22pm

try this link

sqlfiddle

Karthik_Mahalingam 4-Jan-14 23:31pm

its is working fine...

sivamzcet 5-Jan-14 1:20am

but in mysql its not working.. i tried using ctrl c + v and changed column name as per my table column name

Karthik_Mahalingam 5-Jan-14 1:22am

its sql query..
tell me, cursor can be used in mysql ??

sivamzcet 5-Jan-14 1:23am

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
I used this query but the bal value i get is RowNo-1 in both baname

Karthik_Mahalingam 5-Jan-14 1:25am

post your sqlfiddle link.

sivamzcet 5-Jan-14 6:52am

Hyy karthik gud evng!! Sry for the late reply
http://sqlfiddle.com/#!2/9602a/27

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sivamzcet · Accepted Answer · 2014-02-02T06:28:00

Solution 3

Select a.bname, (select b.bal from banktable b where a.baname=b.baname order by b.date desc limit 1) from banktable a group by a.baname

Posted 2-Feb-14 6:28am

sivamzcet