Skip to main content
T
tim19260
Known Participant
February 5, 2011
Answered

subquery in FROM returning error, MySQL

  • February 5, 2011
  • 1 reply
  • 451 views

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.

This topic has been closed for replies.
Correct answer bregent

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

1 reply

B
bregentCorrect answer
Participating Frequently
February 5, 2011

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

T
tim19260Author
Known Participant
February 6, 2011

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!

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices