Skip to main content
O
osgood_
Legend
April 23, 2022
Answered

sql query????

  • April 23, 2022
  • 2 replies
  • 2348 views

Out of curiosity (I dont know much about sql queries) why would the query below flag an error and not work if  'FROM' was to followed directly on after the first instance of Max(departDate) ???? (difficult to see below but there is a break directly before FROM so it goes onto its own line.

 

$excursionMonth = $conn->query("SELECT DISTINCT departMonth, excursionYear, MAX(departDate) 
FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC") or die($conn->error);

 

If FROM is not started on its own line the browser error is:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'excursions GROUP BY departMonth, excursionYear ORDER BY MAX(de' at line 1

 

OR error in sql console:

 

2 errors were found during analysis.

 

  1. An alias was previously found. (near "excursions" at position 80)
  2. Unexpected token. (near "excursions" at position 80)

 

If FROM is on its own line there are no errors and the query works. 

So from that can I assume that some sql statements need to go on their own line?

 

 

 

    This topic has been closed for replies.
    Correct answer B i r n o u

    No need, the current query does the job. It might not be perfect, maybe, but it's functional and returns the required array in the correct order.


    I understand your point,

    so let stuff as they are... at least you should be able to reduce it some

     

    from

    SELECT DISTINCT departMonth, excursionYear, MAX(departDate) FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC

     

    going to

    SELECT departMonth, excursionYear FROM excursions GROUP BY departMonth, excursionYear ORDER BY departDate ASC;

    2 replies

    B i r n o u
    B i r n o u
    Legend
    April 25, 2022

    I'm curious to understand the specific use case of using DISTINCT and GROUP BY on the same column

    O
    osgood_Author
    Legend
    April 25, 2022

    I'm not at all familar with sql queries, l just make them up as l go along until something l need works.

     

    I was for many years using a different approach to getting distinct values and ordering by a specific column but this failed to work once a website lm currently updating was migrated to a different server environment, maybe its running a more up to date mysql version.

     

    Anyway after some experiments the sql query which is shown in the other post returned the results l needed in the specific order l needed. 

    O
    osgood_Author
    Legend
    April 23, 2022

    OK - I think there must have been some invisible shite character gotten into the string of code somehow/somewhere corrupting the statement. When I retyped the complete query from scratch all in one line it worked perfectly!!!

     

    I know its probably better to format the code into several lines anyway but it looks neater and more compact in one line and I work faster that way.

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