sql query????

LEGEND ,
Apr 23, 2022 Apr 23, 2022

Copy link to clipboard

Copied

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?

 

 

 

Views

363

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 2 Correct answers

LEGEND , Apr 23, 2022 Apr 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.

Likes

Translate

Translate
Adobe Community Professional , Apr 25, 2022 Apr 25, 2022

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;

Likes

Translate

Translate
LEGEND ,
Apr 23, 2022 Apr 23, 2022

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

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. 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

what do you expect as returning array single object ?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

An array is returned with distinct values for each month and year ordered by those distinct months and years in the correct order.

 

For instance:

 

April 2022

May 2022

June 2022

July 2022

October 2022

November 2022

January 2023

February 2023

April 2023

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

I reformulate... currently you're using

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

 

so, what do you want to get as result...using word and language 😉

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

I don't understand what you're asking?

 

I already have a functioning sql query that gets me the required results, that's not to say the query is optimal but it works.

 

Sql querying is not something that l know a lot about as usually for me it's just a simple query to get, update, delete etc

 

This query was more complex because months appear in different years and had to be ordered correctly not like:

 

April 2022

April 2023

May 2022

June 2022

June 2023

July 2022

 

April 2023 and June 2023 etc had to come after all months in 2022 and all need to be in the correct month/ year order

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

is it possible for you to send me an sql dump (30 lines not much needed)...

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

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;

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

quote

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;


By @B i r n o u

 

I'll check the query out later tonight, looks less complex and I understand the logic whereas I dont really have a clue what the MAX() bit is supposed to do, unless I take time to look it up.

 

EDITED

I can check it out right now! and yes it works. So I'll use your sql query and not the junk that I wrote even though it works, your's makes more sense.

 

Cheers

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

quote

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;


By @B i r n o u

 

Unfortunatley it didnt work in my other more recent server environment where it's running MAMP:

  • Server version: 5.7.34 - MySQL Community Server (GPL)

 

It worked it my other MAMP server environment but that is running some sql version that was installed at least 5 years back...........hummm

 

I get the following error:

#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

Still no worries the orginal sql query runs ok and returns the correct information.

 

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

cool that it worked, in fact without having the table to simulate the query, and without knowing the point that you was aiming, I just supposed when reading the query itself.

I hope that you wont face any trap, (specific use case) that will broke the scheme.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

quote

Unfortunatley it didnt work in my other more recent server environment where it's running MAMP:

  • Server version: 5.7.34 - MySQL Community Server (GPL)
By @osgood_

 

youps, I spoke too fast... our messages crossed each others,

well are you running MySQL or MariaDB ?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

quote

#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

By @osgood_

 

in that case, you can try adding the filtering column in the previous selection

as

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

quote
quote

#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

By @osgood_

 

in that case, you can try adding the filtering column in the previous selection

as

 

 

 

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

 

 


By @B i r n o u

 

 

I get the below error, similar to last time: 

 

#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

Seriously it doesnt matter. I had a bit of a difficult time getting the original sql query that does work in the right order before it did anything........that's life.....this stuff is fickle.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

is the table big ?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

quote

is the table big ?


By @B i r n o u

 

No, not at all.

 

I think its just one of those quirky instances which is problematical to get right. As I previously stated, I was using a much simpler DISTINCT sql query, which had worked for years locally and worked on a number of different remote sql servers too. That no longer worked when the website I'm currently managing was migrated to a newer remote server environment plus the query didn't work in my newer local server environment either, so I rewrote the query, which is the one which features in this thread and it works in my newer local environment, havent tested it in the remote environment as yet because I dont know if the client wants it upgraded.

 

I'm just doing some exploratory work so I'm in a good position to know how to solve it and I assume if other websites I manage get moved into a different environment they will most probably need the sql query to be upgraded as well.

 

I have no idea why the files were moved to a new remote environment but it happens, the old one could have reached end of life or the host thought it was a good idea, lol. I don't even know if its till the same host......I'm not involved with the hosting of this particular website. I just pick up the pieces and try to glue it back together again.

 

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

I did some test on a remote environement, and wasn't able to reproduce this error... I know what this type of error comes from, but no way to make it happen, now...

 

so here is the Dump , could you try and see if the error happen in your room

 

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
-- Version du serveur : 10.5.15-MariaDB
-- Version de PHP : 7.4.24

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;

-- --------------------------------------------------------

--
-- Structure de la table `excursions`
--

CREATE TABLE `excursions` (
  `id` smallint(6) NOT NULL,
  `departMonth` varchar(10) NOT NULL,
  `excursionYear` varchar(10) NOT NULL,
  `departDate` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Déchargement des données de la table `excursions`
--

INSERT INTO `excursions` (`id`, `departMonth`, `excursionYear`, `departDate`) VALUES
(1, 'avril', '2022', '2022-04-04'),
(2, 'septembre', '2021', '2021-09-07'),
(3, 'avril', '2022', '2022-04-13'),
(4, 'octobre', '2021', '2021-10-05'),
(5, 'avril', '2022', '2022-04-07'),
(6, 'fevrier', '2022', '2022-02-15'),
(7, 'avril', '2021', '2021-04-08'),
(8, 'fevrier', '2021', '2021-02-10'),
(9, 'avril', '2022', '2022-04-04');

--
-- Index pour la table `excursions`
--
ALTER TABLE `excursions`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT pour la table `excursions`
--
ALTER TABLE `excursions`
  MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 26, 2022 Apr 26, 2022

Copy link to clipboard

Copied

Hi Birnou

 

I'll look into this later today, probably this evening when I'm on my other machine with the newer version of sql.

 

Thanks

Os

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 26, 2022 Apr 26, 2022

Copy link to clipboard

Copied

Unfortuantely I still get the same error:

 

#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

I dont know what 'sql_mode' is and I certainly dont want to be changing any of the current settings. The original query works well with the current settings in the local environment. I've yet to test it remotely though, I'll start tinkering at that point, not before.

 

Anyway thanks for your input as always............just seems to be dependent on however the sql server is set up.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 26, 2022 Apr 26, 2022

Copy link to clipboard

Copied

so that mean that my dummy dump is in interrelationship with your own DB

give a last try to check if the query isn't lost by the group

As you can see MariaDB here is on 10.5.15... and the last stable RC is 10.8.2... I think that my VPS should move pretty soon, I probably will see the error... cauz until now, everything works

 

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

 

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 26, 2022 Apr 26, 2022

Copy link to clipboard

Copied

Its has something to do with the sql_mode

 

If I use this:

SET SQL_MODE = '';

before running the query whilst in phpMyAdmin the query works.

 

I dont know how to set SET SQL_MODE = ''; at runtime though in the actual webpage.

 

I've been reading something was changed from sql 5.5 to 5.7 to make this happen. Lots of people seem to have had issues with it from Googling the problem

 

Yes I know I can mess about trying to set this globaly directly in sql BUT at the moment I'd rather not go down that route considering I DO have a query that works. Last time I tried to do anything like that I competely destroyed XAMPP and its never run since, so I have to be careful as I dont want to be left without a server environment!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 26, 2022 Apr 26, 2022

Copy link to clipboard

Copied

quote

I've been reading something was changed from sql 5.5 to 5.7 to make this happen. Lots of people seem to have had issues with it from Googling the problem

By @osgood_

 

if you still have the link at hand, I would be interested... personally I never use column names but aliases.

 

In case I'm going to run an XAMPP including the latest version of MariaDB and see if I can reproduce the error in order to catch the problem, and be able to stop this kind of behavior

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines