0

I have created table :

CREATE TABLE `testForGroupBy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `empId` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Data insetrted :

enter image description here

when I query,

select * from (select * from testForGroupBy order by empId desc)a group by name;

enter image description here

Expeceted :

enter image description here

I googled and found that 5.7 will not work for the partial groupby to make it works need to change sql_mode like below and restarted,

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

But still the same result!

Where am I doing a mistake ?

Barmar
  • 741,623
  • 53
  • 500
  • 612
MMMMS
  • 2,179
  • 9
  • 43
  • 83
  • The change to sql_mode is for getting rid of an error message. If you got results, it has nothing to do with that. – Barmar Sep 13 '19 at 07:23
  • @MadhurBhaiya He didn't get an error, he got unexpected results. – Barmar Sep 13 '19 at 07:23
  • I have done the configuration as said in the https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column but still i didnt get the exact OP – MMMMS Sep 13 '19 at 07:30
  • Can there be duplicate `empId` for the same `name`? – trincot Sep 13 '19 at 07:30
  • @Barmar No error, Mysql restarted successfully. but same OP!? – MMMMS Sep 13 '19 at 07:31
  • @trincot the same query working perfectly in 5.6 version of my production environment. – MMMMS Sep 13 '19 at 07:33
  • @MMMMS If it worked, it was just by accident, it was never guaranteed. See the duplicate question for the correct way to do it. – Barmar Sep 13 '19 at 07:33
  • That was not my question :) Anyway this question has been closed. You'll find the explanation and suggestions in the linked Q&A to make your query valid by SQL standards (which MySql threw out the window in past versions) – trincot Sep 13 '19 at 07:48

1 Answers1

-4

I found out about this type of what I would name unpredictable behavior when I started to work with Postgres and I got the error must appear in the GROUP BY clause or be used in an aggregate function.

A solution for you would be to rewrite the query like:

select id, name, MAX(empId) from testForGroupBy group by name;

Please not that the value for id would also be "unpredictable" for the grouped records.

Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
  • 2
    That won't return the correct `id` value. – Barmar Sep 13 '19 at 07:25
  • what would be the *correct* `id` value in this case? That's exactly why I mentioned `Postgres`'s behavior before writing the query and I mentioned again that `id` is *unpredictable*, aka *not always correct* – Tudor Constantin Sep 13 '19 at 07:42
  • 1
    From his desired output, it looks like he wants the id to be correct. Since there's an easy way to get that result, why would you post this? See the duplicate question if you aren't familiar with it, it should be in every MySQL programmer's bag of tricks. – Barmar Sep 13 '19 at 07:44