how to find max in several tables

Dear all.

I'm desperated.

I need to find one max value from several identical tables using cluasule
"where". Fortumately column taken into where clausule is the same in all
tables - it is datestamp.

I lost and I dont know how it should be.

Please help.

Marek.

> I'm desperated.
I think I was too early ;)))

Some googling and comparing examples more and I have proposal.

I will describe it for others but I have some thoughts to discuss.

> I need to find one max value from several identical tables using cluasule
> "where". Fortumately column taken into where clausule is the same in all
> tables - it is datestamp.

select value as MaxValue from
(
select (select value from table_0 order by date desc limit 1) as value
union all select (select value from table_1 order by date desc limit 1)
union all select (select value from table_2 order by date desc limit 1)
union all select (select value from table_3 order by date desc limit 1)
) as a order by value desc limit 1

You can put "union all select (select value from table_x order by date desc
limit 1)" as many as you need.

PHP code can look like (count is amount of tables to find this max value
between):

$sql="
select value as MaxValue from
(
select (select value from table_0 order by date desc limit 1) as
value\n";

for($i=0;$i<$count;$i++)
$sql.="union all select (select value from table_".$i." order by date
desc limit 1)\n";

$sql.="
) as a order by value desc limit 1";

-------------------------------------------

Above was for someone desperated like me.

But after first success I started to compare two solutions.

* **
I didn't mention that before I started this subject I solve my problem using
PHP:

for($i=0;$i<$ilosc;$i++)
{
$sql = "select value from table_".$i." order by date desc limit 1";

$result = $db->req($sql);

if (!$result)
{
$db->err();
exit(0);
}

$var=$db->row($result);

$value[$i]=$var['value'];
}

arsort($value);

$vKey=key($value);

echo $value[$vKey];
***

And conclusion is quite surprising.

In practice (from time mesurment perspective) there is no difference between
those methods (!)

Look [cycle: difference = (php based method - mysql based method)]

Mesurment in miliseconds. Cycle by secounds, test extends minute

0: -0.38760232925415 = (0.43427181243896 - 0.82187414169312)
1: -0.041707038879395 = (0.89149689674377 - 0.93320393562317)
2: 0.019755125045776 = (0.45040416717529 - 0.43064904212952)
3: -0.087251901626587 = (0.71567702293396 - 0.80292892456055)
4: 0.19066834449768 = (0.85719323158264 - 0.66652488708496)
5: -0.0030617713928223 = (0.41993713378906 - 0.42299890518188)
6: 0.048226118087769 = (0.88823103904724 - 0.84000492095947)
7: 0.43411803245544 = (0.8612949848175 - 0.42717695236206)
8: -0.24436712265015 = (0.42300391197205 - 0.66737103462219)
9: 0.083009958267212 = (0.9006199836731 - 0.81761002540588)
10: 0.22547793388367 = (0.64996910095215 - 0.42449116706848)
11: -0.29272294044495 = (0.55686902999878 - 0.84959197044373)
12: 0.0071518421173096 = (0.80980587005615 - 0.80265402793884)
13: 0.0051150321960449 = (0.41917300224304 - 0.414057970047)
14: -0.15197491645813 = (0.74451994895935 - 0.89649486541748)
15: 0.168781042099 = (0.79198408126831 - 0.62320303916931)
16: -0.126629114151 = (0.4208459854126 - 0.5474750995636)
17: -0.010192155838013 = (0.8590350151062 - 0.86922717094421)
18: 0.33948302268982 = (0.76075601577759 - 0.42127299308777)
19: -0.3118839263916 = (0.42143511772156 - 0.73331904411316)
20: -0.0088319778442383 = (0.8840000629425 - 0.89283204078674)
21: 0.102374792099 = (0.52316284179688 - 0.42078804969788)
22: -0.26321029663086 = (0.62160992622375 - 0.88482022285461)
23: 0.2661919593811 = (0.88895201683044 - 0.62276005744934)
24: -0.022083044052124 = (0.41466999053955 - 0.43675303459167)
25: 0.17751598358154 = (1.025563955307 - 0.84804797172546)
26: 0.27879023551941 = (0.7008171081543 - 0.42202687263489)
27: -0.45249223709106 = (0.40760087966919 - 0.86009311676025)
28: 0.16680288314819 = (0.96263790130615 - 0.79583501815796)
29: -0.011406183242798 = (0.41220498085022 - 0.42361116409302)
30: -0.010564088821411 = (0.86033701896667 - 0.87090110778809)
31: 0.41592383384705 = (0.83277487754822 - 0.41685104370117)
32: -0.32411885261536 = (0.40960907936096 - 0.73372793197632)
33: -0.017750024795532 = (0.94556403160095 - 0.96331405639648)
34: -0.63694596290588 = (0.92752385139465 - 1.5644698143005)
35: 0.1849672794342 = (1.8622071743011 - 1.6772398948669)
36: -0.4061279296875 = (1.0174160003662 - 1.4235439300537)
37: 0.08779501914978 = (0.94684815406799 - 0.85905313491821)
38: 0.10975027084351 = (0.53631210327148 - 0.42656183242798)
39: -0.13911890983582 = (0.64859199523926 - 0.78771090507507)
40: 0.38515400886536 = (0.98991107940674 - 0.60475707054138)
41: -0.13822817802429 = (0.4227499961853 - 0.56097817420959)
42: -0.012900114059448 = (0.82146596908569 - 0.83436608314514)
43: 0.42620491981506 = (0.85716700553894 - 0.43096208572388)
44: -0.36759209632874 = (0.42792797088623 - 0.79552006721497)
45: 0.026980876922607 = (0.83605098724365 - 0.80907011032104)
46: 0.20211601257324 = (0.62575697898865 - 0.42364096641541)
47: -0.22629618644714 = (0.60691094398499 - 0.83320713043213)
48: 0.24607133865356 = (0.91456818580627 - 0.66849684715271)
49: -0.0096259117126465 = (0.41812205314636 - 0.42774796485901)
50: 0.026109933853149 = (0.92834305763245 - 0.9022331237793)
51: 0.33640694618225 = (0.75980806350708 - 0.42340111732483)
52: -0.38195610046387 = (0.41404485702515 - 0.79600095748901)
53: -0.016902923583984 = (0.85121893882751 - 0.8681218624115)
54: 0.084506034851074 = (0.5047299861908 - 0.42022395133972)
55: -0.24564099311829 = (0.63632202148438 - 0.88196301460266)
56: 0.20311307907104 = (0.85685706138611 - 0.65374398231506)
57: -0.04003381729126 = (0.40406799316406 - 0.44410181045532)
58: 0.061955213546753 = (0.91458916664124 - 0.85263395309448)
59: 0.38146305084229 = (0.80579590797424 - 0.42433285713196)

average: 0.005046017964681 (!?)

So maybe (which is almost for sure) my sql statement is not efficient enough
?

But if there was no difference which method to choose ? Maybe sql based
because it is not too healthy to bomb sql server with number requests in
very short time ?

Marek.

P.S.

I will write mysql procedure yet and I will test it also.

> select (select value from table_0 order by date desc limit 1) as
> value\n";
[...] I have eror here. counter should start from 1 in this example because
in other way table_0 will be scaned two times.

> for($i=0;$i<$count;$i++)
[...]

> I will write mysql procedure yet and I will test it also.

It is not so simple ... I cant using PREPARE in function ... so I don't know
how to build and execute receiving data from dynamic statement.

(as for now ;)))))) )

> So maybe (which is almost for sure) my sql statement is not efficient
> enough ?

Yes it was.

Remember young programmer !

Put proper key on column that is the main in request. I changed primary key
from "id" to "date" and I received what I expected.

SQL statemet based aproach is at least 5 times faster in my test then PHP
loop based one.

Good luck ;)))

Marek.