subquery in FROM returning error, MySQL

[tim19260]

Hi,

I am trying to order by date first (descending), then group by and order by last name. Tried just order by and group by, but didn't work. I'm now trying to get there through a subquery, the subquery works as a query on it's own, but I'm running into syntax errors when I make it a subquery. Here's my code:

SELECT * FROM (SELECT clientrogue_info.id_clientrogue AS idrogue, clientrogue_info.roguefirst AS first, clientrogue_info.roguelast AS last, provider_cvpv.idprovidercvpv AS cvpvid, provider_cvpv.providerid AS providerid FROM clientrogue_info LEFT JOIN provider_cvpv on provider_cvpv.cpid = clientrogue_info.id_clientrogue
ORDER BY visitdate DESC) AS t1 GROUP BY t1.last ORDER BY t1.last

Thanks in advance for help.

[bregent · Accepted solution]

That's not really a subquery, it's a derived table. In most DBMS's, the main select list must reference the derived table's alias.There may be other problems but that's the first thing to jump out at me.

Try:

SELECT t1.* FROM (SELECT clientrogue_info.id_clientrogue AS idrogue,  clientrogue_info.roguefirst AS first, clientrogue_info.roguelast AS  last, provider_cvpv.idprovidercvpv AS cvpvid, provider_cvpv.providerid  AS providerid FROM clientrogue_info LEFT JOIN provider_cvpv on  provider_cvpv.cpid = clientrogue_info.id_clientrogue
ORDER BY visitdate DESC) AS t1 GROUP BY t1.last ORDER BY t1.last

[tim19260]

Thanks for the info on derived tables bregent,

That did the trick, I hadn't used an alias for the table. It works exactly like I want it too!