[mysql] wyciagniecie ostatniego rekordu wraz z GROUP BY Opcje

[AndyPSV2]

zapytanie jakie mam:

[SQL] pobierz, plaintext 
SELECT *,COUNT(*) AS cnt FROM '.PRFX.'messages WHERE _to = "'.$this->u['id'].'" GROUP BY _from ORDER BY id DESC LIMIT '.($pg*$PER_PG).','.$PER_PG.'')
[SQL] pobierz, plaintext 

[XML] pobierz, plaintext 
	id	_from	_to	date	message	read
			1	3	1	2011-11-07 13:49:54	dasdsasaddsadsa	0
			2	3	1	2011-11-07 13:50:18	newest message 	0
[XML] pobierz, plaintext 

wynik jaki dostaje:

[HTML] pobierz, plaintext 
array ( 0 => array ( 'id' => '1', '_from' => '3', '_to' => '1', 'date' => '2011-11-07 13:49:54', 'message' => 'dasdsasaddsadsa', 'read' => '0', 'cnt' => '2', ), )
[HTML] pobierz, plaintext 

wynik jaki chce uzyskac:

[PHP] pobierz, plaintext 
array ( 0 => array ( 'id' => '2', '_from' => '3', '_to' => '1', 'date' => '2011-11-07 13:50:18', 'message' => 'newest message', 'read' => '0', 'cnt' => '2', ), )
[PHP] pobierz, plaintext 

jak to zrobic?

kombinowalem roznie, ale nie wyszlo np.

[SQL] pobierz, plaintext 
'SELECT id,date,message,COUNT(*) as cnt FROM (SELECT MAX(date) AS date,message FROM '.PRFX.'messages WHERE _to = "'.$this->u['id'].'" GROUP BY _from ORDER BY date DESC) LEFT JOIN '.PRFX.'messages USING (date,message) LIMIT '.($pg*$PER_PG).','.$PER_PG.''
[SQL] pobierz, plaintext 

[Sephirus]

[SQL] pobierz, plaintext 
    SELECT *,COUNT(*)  AS cnt FROM (SELECT * FROM '.PRFX.'messages WHERE _to = "'.$this->u['id'].'" ORDER BY id DESC) x GROUP BY _fromR ORDER BY id DESC LIMIT '.($pg*$PER_PG).','.$PER_PG.'')
 
[SQL] pobierz, plaintext 

Ten post edytował Sephirus 7.11.2011, 15:52:05