query efficiency

--1355153491-2091569531-1317126750=:38612
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Here's a simple query: select name,status,min(dt), max(dt),count(*)=0A=
from mytable=0Agroup by name I need an efficient way to get this data,=
along with the status of the row with the max(dt). =A0'status' is not in t=
he group by, so I get apparently random statuses. =A0Is there any way to do=
this without a table join? =A0Or I guess the real question is: =A0What is =
the most efficient way of accomplishing this? =A0The table is huge, 25M row=
s or so, but I can create any indexes.
--1355153491-2091569531-1317126750=:38612--

Re: query efficiency

>>>> 2011/09/27 05:32 -0700, supr_star >>>>
Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this?
<<<<<<<<
I cannot answer the real question, but there is this:

select name,(SELECT status
FROM mytable AS x
WHERE x.name = mytable.name AND x.dt = max(mytable.dt)) AS status,
min(dt), max(dt),count(*) from mytable group by name

I will not vouch that this is more efficient than joining.

(Surely there are enough of this that this is entitled to a special SQL construct.)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org