MYSQL: How to select last record from select query

[red_ruewei]

Hi all,
I try to view latest date and amount from history table for each branch (branch table :viscawangan).

But accouter problem to view amount value.

SELECT b.kod_cawangan,b.nama_cawangan, MAX( a.tkh_data ) AS tarikh_data,[B]a.amount[/B]
FROM history a, viscawangan b
WHERE a.kod_caw = b.kod_cawangan
GROUP BY kod_caw

Here i give example for amount value that suppose to show with branch kod_caw as 'A2'.

If i run below query, it will show all amount for branch code A2.So amount value that suppose to view for branch A2 is 720 which is last record from below query statement.Please refer image as attach for record viewing

SELECT  amount FROM history WHERE kod_caw = 'A2'

.

I'm thinking to select last record from above selection query to handle this problem. But still stuck until now and dont know how. I try to use this example

SELECT * FROM users ORDER BY userid DESC LIMIT 1

but it is for last record of table not last record of query selection.

Hope anyone can give an idea or helps.
Really much appreciate for the respond.

Thanks in advance

For table record can download in here

[tesuji]

Glad to meet you again.

Are you still suffering from the old database problem we were talken about in this thread? What about the ERM fragment I sent you in the very last post of that thread, wasn't there any chance to improve data model?

As for your first select, yes, this cannot function for the GROUP BY is plain wrong, (it might work like game of dice or even French roulette ;)). You may read here and some more details are there on wrong usage of GROUP BY clause.

-- tesu

[red_ruewei]

oh..thanks for reply.this one is diff from previous thread that i post:icon_cheesygrin:

Thanks for the ERM fragment also.The problem related to existing design that not relevant that bring out this issue. hehe.I also new here,but my colleague decide to do other way that not reflect existing design.:$

tesu,
for this problem,i had take a look on example u gave me.it really help. So now i know got problem with group by ;)

I try like this

SELECT b.kod_cawangan,b.nama_cawangan,a.amount, MAX( a.tkh_data ) AS tarikh_data
FROM history a, viscawangan b
WHERE a.kod_caw = b.kod_cawangan
GROUP BY b.kod_cawangan,b.nama_cawangan,a.amount

it become like this (refer attachment)
..

sorry to trouble you. but i dont know how...:'(

[tesuji]

Hi red_ruewei,

the result corresponds completely to your select statement. However, it seems that it does not meet your concept. So what's wrong, what should be the exact result? Can you give an example? (Unfortunately, I have deleted the data once you sent to me. So I am unable to look inside the problem).

-- tesu

[red_ruewei]

thanks tesu for the reply

this relate to 2 table which is history n viscawangan(brach info)
in viscawangan, store all branch information like branch name,branch code while
in history it store date of history of branch

example record in viscawangan
---------------------------
branch_code | branch_name |
---------------------------
805         | Klang       |
8003        | Jitra       |
---------------------------

example in history table
----------------------------------------------
branch_code | date_update | amount_of_people |
----------------------------------------------
805         | 20/10/2010  | 800              |
805         | 21/10/2010  | 789              |
805         | 22/10/2010  | 801              |
8003        | 19/02/2010  | 200              |
8003        | 23/03/2010  | 201              | 
----------------------------------------------

so the result will be
------------------------------------
branch_name | date_latest | amount |
------------------------------------
Klang       | 22/10/2010  | 801    |
Jitra       | 23/03/2010  | 201    |
------------------------------------

problem that i've been hit is amount value...
hope u can help.
thanks in advance

[tesuji]

Selamat Siang

Tell me what will be the result if you carry out this:

select v.branch_code, v.branch_name, h.date_update as date_latest, h.amount_of_people as amount
  from viscawangan v join historytable h on v.branch_code = h.branch_code
   where h.date_update=(select max(date_update) from historytable where v.branch_code = branch_code) order by v.branch_name;

You may examine all words and syntax, there might be typos etc.

-- tesu

[pavan_teja]

Hi dude,

Once try this....

You said you want to select the last record, So i suggest you use rownum function....

SELECT b.kod_cawangan,b.nama_cawangan, MAX( a.tkh_data ) AS tarikh_data,a.amount
FROM

(SELECT b.kod_cawangan,b.nama_cawangan, MAX( a.tkh_data ) AS tarikh_data,a.amount
FROM history a, viscawangan b
WHERE a.kod_caw = b.kod_cawangan
GROUP BY kod_caw)

SORT DESC
WHERE ROWNUM=1;

This could work... If there are any syntax errors, do make some modifications here and there and it should work...
Good Luck.

[red_ruewei]

Selamat Siang

Tell me what will be the result if you carry out this:

select v.branch_code, v.branch_name, h.date_update as date_latest, h.amount_of_people as amount
  from viscawangan v join historytable h on v.branch_code = h.branch_code
   where h.date_update=(select max(date_update) from historytable where v.branch_code = branch_code) order by v.branch_name;

You may examine all words and syntax, there might be typos etc.

-- tesu

Selamat Siang, tesu
I already run the example u gave me. It take a long time to execute. after 30min.still not viewing the result:'(

[red_ruewei]

Hi dude,

Once try this....

You said you want to select the last record, So i suggest you use rownum function....

SELECT b.kod_cawangan,b.nama_cawangan, MAX( a.tkh_data ) AS tarikh_data,a.amount
FROM

(SELECT b.kod_cawangan,b.nama_cawangan, MAX( a.tkh_data ) AS tarikh_data,a.amount
FROM history a, viscawangan b
WHERE a.kod_caw = b.kod_cawangan
GROUP BY kod_caw)

SORT DESC
WHERE ROWNUM=1;

This could work... If there are any syntax errors, do make some modifications here and there and it should work...
Good Luck.

Hi pavan_teja,

thanks for reply.
I cannot run ur example. Hit rownum error. I try to google it and found below code in order to view rownum in mysql

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, history t;

i'll let you know once i able to execute it...:)

[tesuji]

Well, my suggested query should meet your requirements. Can you examine how many rows this join viscawangan v JOIN historytable h on v.branch_code = h.branch_code includes by executing:

select count(*) from viscawangan v JOIN historytable h on v.branch_code = h.branch_code;

The drawback of my suggested query is that it uses a correlated query (WHERE v.branch_code = branch_code) what becomes very slow if a plenty of rows are affected and no index or key can be used.

One can speed up correlated queries by putting indexes/keys on the appropriate columns. In your case the max(date_update) is the obstacle. Therefore you could index the important column date_update of historytable. And you should then use EXPLAIN to examine whether this newly generated index is really used in the correlated queries.

I am about to thinking over whether these correlated queries might be replaced by better query not having correlations, however, I am afraid there is no other solution. If so, only additional index/key on column date_update of historytable wil be able to speed up calculation considerably.

-- tesu

[red_ruewei]

Well, my suggested query should meet your requirements. Can you examine how many rows this join viscawangan v JOIN historytable h on v.branch_code = h.branch_code includes by executing:

select count(*) from viscawangan v JOIN historytable h on v.branch_code = h.branch_code;

The drawback of my suggested query is that it uses a correlated query (WHERE v.branch_code = branch_code) what becomes very slow if a plenty of rows are affected and no index or key can be used.

One can speed up correlated queries by putting indexes/keys on the appropriate columns. In your case the max(date_update) is the obstacle. Therefore you could index the important column date_update of historytable. And you should then use EXPLAIN to examine whether this newly generated index is really used in the correlated queries.

I am about to thinking over whether these correlated queries might be replaced by better query not having correlations, however, I am afraid there is no other solution. If so, only additional index/key on column date_update of historytable wil be able to speed up calculation considerably.

-- tesu

Tesu,
The count record is 10585.

[tesuji]

Tesu,
The count record is 10585.

Well, 10585 rows is something heavy for correlated selects. Can you tell me the indexes and keys which are chosen for this query? Simply run

explain 
select v.branch_code, v.branch_name, h.date_update as date_latest, h.amount_of_people as amount
  from viscawangan v join historytable h on v.branch_code = h.branch_code
   where h.date_update=(select max(date_update) from historytable where v.branch_code = branch_code) order by v.branch_name;

Then post its result. Also post your original select what you have derived from mine.

You see the max(date_update) above. Can you estimate from how many row per branch_code the max() is taken?

I think, if you put an index on (branch_code ASC, date_update DESC), execution time will be substantially reduced.

-- tesu

[red_ruewei]

thanks for reply. My answer as below

Well, 10585 rows is something heavy for correlated selects. Can you tell me the indexes and keys which are chosen for this query? Simply run

explain 
select v.branch_code, v.branch_name, h.date_update as date_latest, h.amount_of_people as amount
  from viscawangan v join historytable h on v.branch_code = h.branch_code
   where h.date_update=(select max(date_update) from historytable where v.branch_code = branch_code) order by v.branch_name;

Then post its result. Also post your original select what you have derived from mine.

You see the max(date_update) above. Can you estimate from how many row per branch_code the max() is taken?

I think, if you put an index on (branch_code ASC, date_update DESC), execution time will be substantially reduced.

-- tesu

For result can refer attachment.

For original select what I have derived from your as below

EXPLAIN
SELECT v.kod_cawangan, v.nama_cawangan, h.tkh_data AS date_latest, h.amount AS amount
FROM viscawangan v JOIN history h ON v.kod_cawangan = h.kod_caw
WHERE h.tkh_data=(SELECT MAX(tkh_data) FROM history WHERE v.kod_cawangan = kod_caw) ORDER BY v.nama_cawangan;

[tesuji]

Hi

Thanks for posting your data.

Finally I figured out why my suggested query never ends on your Mysql database if there are just 10585 rows affected. The reason is that simple and also that frustrated: MySQL does NOT have effective Query Optimizer. I found that indirectly by calculating the execution plans for my select statement on those databases: MS SQL Sever, Oracle and Sybase SQL Anywhere. All these three database systems replace slow correlated selects by fast table join operations! Query optimizing is quite usual and virtually almost all database systems have this useful functionality except mysql.

On Sybase database I have SQL test tools for testing and optimizing select statements by means of automatically generated test data. I fed the test tool with the select statement in question, defined your both table and necessary data pattern for automatic generation of test data.

Testing was carried out with the following volume: table viscawangan 10585 rows (your number), historytable 105850 rows. I decided that each branch_code should have 10 randomly generated date_update and amount_of_people values on average, that is 10 times 10585. So the total amount of rows processed in first inner join was 116435 rows. The result set contained 9843 rows something less than viscawangan rows for I allowed that also viscawangan rows were generated without entries in historytable.

Testing result for this scenario: Execution time 0.445 seconds (less than a half second). Well, that's it, the result of a true relational database system.

Possibly the only improvement I did, as compared with your both tables, I put primary key (branch_code) on table viscawangan and primary key(branch_code, date_update) on historytable which might also speed-up execution.

Btw, this testing results also proves that the suggested query provides correct results as you desired.

-- tesu

[red_ruewei]

hi tesu,
u r my hero.:*:cool::P

I didnt manage to alter kod_caw and tkh_data as primary key but created index instead.

After run below query, the result is successfully view as what i want.

SELECT v.kod_cawangan, v.nama_cawangan, h.tkh_data AS date_latest, h.amount AS amount
FROM viscawangan v JOIN history h ON v.kod_cawangan = h.kod_caw
WHERE h.tkh_data=(SELECT MAX(tkh_data) FROM history WHERE v.kod_cawangan = kod_caw) ORDER BY v.nama_cawangan;

thanks also for ur very useful explanation.
u really make my day.
thanks again sifu:)

result as attach