Sfrgttk

Does the kobold player race feature, Pack Tactics, give ranged attacks advantage?

How can a kingdom keep the secret of a missing monarch from the public?

Ramanujan's radical and how we define an infinite nested radical

Hollowed circle with crossed line and arrow

Are there rules for falling in water vs. diving vs. just jumping in for various depths of water?

ErrorListPlot crops error bars

How can I add more depth to my poem?

Is Apex Sometimes Case Sensitive?

Is it possible to detect 100% of SQLi with a simple regex?

Why is Shelob considered evil?

How to play song that contains one guitar when we have two guitarists (or more)?

Why didn't Lorentz conclude that no object can go faster than light?

How do I write a maintainable, fast, compile-time bit-mask in C++?

Under which circumstances can »werden« stand at the end of a sentence?

Manager has noticed coworker's excessive breaks. Should I warn him?

The Late Queen Gives in to Remorse - Reverse Hangman

Is it appropriate to give a culturally-traditional gift to a female coworker?

Get category id

Why do BLDC motor (1kW) controllers have so many mosfets?

Why did some CPUs use two Read/Write lines, and others just one?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

Why does finding small effects in large studies indicate publication bias?

How can changes in personality/values of a person who turned into a vampire be explained?

Sub query not working as expected trying to order by then group by, MySQL 5.5 vs 5.7

MySQL row length is almost the triple of what I expect“Lost connection to MySQL server during query” errorMysql join not workingMySQL group concatenation - creation of insert statement on temp tableAlternative to removing ONLY_FULL_GROUP_BYFull GROUP BY in MySQL 5.7 breaks de-dupe queriesIncompatible with sql_mode:only_full_group_byTroubles with: sql_mode=only_full_group_byProblem with only_full_group_byGetting the latest value using unique ID and user ID in MySQL

0

I do not know/understand DB's as well as I would like. I'm trying to ORDER BY and then GROUP BY so that I get the first record from each group of results in the database.

-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)



CREATE TABLE IF NOT EXISTS `docs` (

  `field1` int(6) unsigned NOT NULL,

  `field2` int(6) unsigned NOT NULL,

  `field3` int(6) NOT NULL,

  `field4` int(6) NOT NULL,

  PRIMARY KEY (`field1`)

) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES

  ('27908', '82', '1', '17'),

  ('27907', '82', '1', '50'),

  ('402', '25', '1', '90'),

  ('312', '25', '10', '8');

If I ORDER BY the results are as expected. Note it works without sub-query. I'm just breaking apart my original query:

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

)

Result (formmated with table generator):

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|    312 |     25 |     10 |      8 |

|  27908 |     82 |      1 |     17 |

|  27907 |     82 |      1 |     50 |

+--------+--------+--------+--------+

The ORDER BY then GROUP BY works as expected in MySQL v5.5:

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

) temp

GROUP BY field2

Result:

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|  27908 |     82 |      1 |     17 |

+--------+--------+--------+--------+

But it no longer works in v5.7 and results in error:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.field1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I don't understand how to fix when using a sub-query or another way to write the query that works in v5.7. How can this query be changed to work in MySQL v5.7?

mysql mysql-5.7

share

asked 4 mins ago

learnsomemorelearnsomemore

1

New contributor

learnsomemore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I do not know/understand DB's as well as I would like. I'm trying to ORDER BY and then GROUP BY so that I get the first record from each group of results in the database.

-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)



CREATE TABLE IF NOT EXISTS `docs` (

  `field1` int(6) unsigned NOT NULL,

  `field2` int(6) unsigned NOT NULL,

  `field3` int(6) NOT NULL,

  `field4` int(6) NOT NULL,

  PRIMARY KEY (`field1`)

) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES

  ('27908', '82', '1', '17'),

  ('27907', '82', '1', '50'),

  ('402', '25', '1', '90'),

  ('312', '25', '10', '8');

If I ORDER BY the results are as expected. Note it works without sub-query. I'm just breaking apart my original query:

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

)

Result (formmated with table generator):

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|    312 |     25 |     10 |      8 |

|  27908 |     82 |      1 |     17 |

|  27907 |     82 |      1 |     50 |

+--------+--------+--------+--------+

The ORDER BY then GROUP BY works as expected in MySQL v5.5:

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

) temp

GROUP BY field2

Result:

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|  27908 |     82 |      1 |     17 |

+--------+--------+--------+--------+

But it no longer works in v5.7 and results in error:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.field1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I don't understand how to fix when using a sub-query or another way to write the query that works in v5.7. How can this query be changed to work in MySQL v5.7?

mysql mysql-5.7

share

asked 4 mins ago

learnsomemorelearnsomemore

1

New contributor

learnsomemore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I do not know/understand DB's as well as I would like. I'm trying to ORDER BY and then GROUP BY so that I get the first record from each group of results in the database.

-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)



CREATE TABLE IF NOT EXISTS `docs` (

  `field1` int(6) unsigned NOT NULL,

  `field2` int(6) unsigned NOT NULL,

  `field3` int(6) NOT NULL,

  `field4` int(6) NOT NULL,

  PRIMARY KEY (`field1`)

) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES

  ('27908', '82', '1', '17'),

  ('27907', '82', '1', '50'),

  ('402', '25', '1', '90'),

  ('312', '25', '10', '8');

If I ORDER BY the results are as expected. Note it works without sub-query. I'm just breaking apart my original query:

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

)

Result (formmated with table generator):

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|    312 |     25 |     10 |      8 |

|  27908 |     82 |      1 |     17 |

|  27907 |     82 |      1 |     50 |

+--------+--------+--------+--------+

The ORDER BY then GROUP BY works as expected in MySQL v5.5:

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

) temp

GROUP BY field2

Result:

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|  27908 |     82 |      1 |     17 |

+--------+--------+--------+--------+

But it no longer works in v5.7 and results in error:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.field1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I don't understand how to fix when using a sub-query or another way to write the query that works in v5.7. How can this query be changed to work in MySQL v5.7?

mysql mysql-5.7

share

asked 4 mins ago

learnsomemorelearnsomemore

1

New contributor

learnsomemore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)



CREATE TABLE IF NOT EXISTS `docs` (

  `field1` int(6) unsigned NOT NULL,

  `field2` int(6) unsigned NOT NULL,

  `field3` int(6) NOT NULL,

  `field4` int(6) NOT NULL,

  PRIMARY KEY (`field1`)

) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES

  ('27908', '82', '1', '17'),

  ('27907', '82', '1', '50'),

  ('402', '25', '1', '90'),

  ('312', '25', '10', '8');

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

)

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|    312 |     25 |     10 |      8 |

|  27908 |     82 |      1 |     17 |

|  27907 |     82 |      1 |     50 |

+--------+--------+--------+--------+

SELECT * FROM 

(

SELECT * FROM docs

ORDER BY field2, field1 DESC

) temp

GROUP BY field2

+--------+--------+--------+--------+

| field1 | field2 | field3 | field4 |

+--------+--------+--------+--------+

|    402 |     25 |      1 |     90 |

|  27908 |     82 |      1 |     17 |

+--------+--------+--------+--------+

share

asked 4 mins ago

learnsomemorelearnsomemore

1

New contributor

learnsomemore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 4 mins ago

learnsomemorelearnsomemore

1

New contributor

learnsomemore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

learnsomemorelearnsomemore

1

New contributor

learnsomemore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

learnsomemorelearnsomemore

1