Styjun

In the Marvel universe, can a human have a baby with any non-human?

What is the origin of Scooby-Doo's name?

Cascading Repair Costs following Blown Head Gasket on a 2004 Subaru Outback

How dangerous are set-size assumptions?

C-152 carb heat on before landing in hot weather?

Can humans ever directly see a few photons at a time? Can a human see a single photon?

How long would it take to cross the Channel in 1890's?

3D Crossword, Cryptic, Statue View & Maze

What was the Shuttle Carrier Aircraft escape tunnel?

Impossible darts scores

Trainee keeps missing deadlines for independent learning

Why do all the teams that I have worked with always finish a sprint without completion of all the stories?

Has there been any indication at all that further negotiation between the UK and EU is possible?

How risky is real estate?

Is my Rep in Stack-Exchange Form?

If the world have massive single giant world tree can it stop earthquake?

Interaction between Leyline of Anticipation and Teferi, Time Raveler

Are all instances of trolls turning to stone ultimately references back to Tolkien?

Can the negators "jamais, rien, personne, plus, ni, aucun" be used in a single sentence?

Links to webpages in books

Can ADFS connect to other SSO services?

How do I respond to requests for a "guarantee" not to leave after a few months?

Should my manager be aware of private LinkedIn approaches I receive? How to politely have this happen?

Underbar nabla symbol doesn't work

can this query be rebuilt to be more optimized?

order by importance then group by sectionHow can I prevent SQL injection in PHP?How to output MySQL query results in CSV format?How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?Get top count in a table?GROUP BY vs INSERT IGNORESelect from three tables - mysql and phpHow to export and import the databases in MySql.Is there a possibility to avoid two subqueries which access the same table?What is better in performance for 1-to-1 relationship: a new table or a VARCHAR column with a lot of empty entriesSQL Syntax Error - Can't Import

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

1

sqlfiddle: http://sqlfiddle.com/#!9/a209c7/2/0

my table is:

-- borrowed from https://stackoverflow.com/questions/55325155

CREATE TABLE IF NOT EXISTS `docs` (
 `id` int(6) unsigned NOT NULL,
 `section` int(2) unsigned NOT NULL,
 `rev` varchar(3) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `section`, `rev`) VALUES
 ('1', '1', 'a'),
 ('2', '1', 'b'),
 ('3', '2', 'c'),
 ('4', '3', 'd'),
 ('5', '3', 'e');

CREATE TABLE IF NOT EXISTS `docs_importance` (
 `doc_id` int(6) unsigned NOT NULL,
 `importance` int(3) unsigned NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs_importance` (`doc_id`, `importance`) VALUES
 ('1', '1'),
 ('1', '2'),
 ('1', '2'),
 ('2', '2'),
 ('2', '4'),
 ('3', '9'),
 ('4', '5'),
 ('5', '6');

and my query is:

-- based on answer https://stackoverflow.com/questions/55325155

SELECT t.id, t.section, t.rev, t.importance 
FROM (SELECT docs.id, docs.section, docs.rev, (SELECT SUM(docs_importance.importance) 
 FROM docs_importance 
 WHERE docs_importance.doc_id = docs.id) AS importance
 FROM docs
 GROUP BY docs.id
 ORDER BY importance DESC
 ) AS t
GROUP BY t.section;

---

what i am doing is to order the rows of docs by its calculated sum of importance then group it by its section to get the highest doc in importance for each section only

can this query be remade in a better way and faster?

mysql mariadb greatest-n-per-group query-performance

share|improve this question

edited Mar 25 at 9:39

Joe Doe

asked Mar 25 at 9:30

Joe DoeJoe Doe

3866 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  which version of MySQL you are using?
  
  – vishal
  Mar 25 at 9:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @vishal MySQL 5.6 according to MariaDB version also I don't understand CTEs queries and WITH statements very good.
  
  – Joe Doe
  Mar 25 at 9:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have a PRIMARY KEY on each table. Read some of the Q&A at [greatest-n-per-group]. Implement some variant of what you find there. Then update the Question to show us how far you got.
  
  – Rick James
  Apr 17 at 3:07
  

  
  
  
  

add a comment | 

2

1

sqlfiddle: http://sqlfiddle.com/#!9/a209c7/2/0

my table is:

-- borrowed from https://stackoverflow.com/questions/55325155

CREATE TABLE IF NOT EXISTS `docs` (
 `id` int(6) unsigned NOT NULL,
 `section` int(2) unsigned NOT NULL,
 `rev` varchar(3) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `section`, `rev`) VALUES
 ('1', '1', 'a'),
 ('2', '1', 'b'),
 ('3', '2', 'c'),
 ('4', '3', 'd'),
 ('5', '3', 'e');

CREATE TABLE IF NOT EXISTS `docs_importance` (
 `doc_id` int(6) unsigned NOT NULL,
 `importance` int(3) unsigned NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs_importance` (`doc_id`, `importance`) VALUES
 ('1', '1'),
 ('1', '2'),
 ('1', '2'),
 ('2', '2'),
 ('2', '4'),
 ('3', '9'),
 ('4', '5'),
 ('5', '6');

and my query is:

-- based on answer https://stackoverflow.com/questions/55325155

SELECT t.id, t.section, t.rev, t.importance 
FROM (SELECT docs.id, docs.section, docs.rev, (SELECT SUM(docs_importance.importance) 
 FROM docs_importance 
 WHERE docs_importance.doc_id = docs.id) AS importance
 FROM docs
 GROUP BY docs.id
 ORDER BY importance DESC
 ) AS t
GROUP BY t.section;

---

what i am doing is to order the rows of docs by its calculated sum of importance then group it by its section to get the highest doc in importance for each section only

can this query be remade in a better way and faster?

mysql mariadb greatest-n-per-group query-performance

share|improve this question

edited Mar 25 at 9:39

Joe Doe

asked Mar 25 at 9:30

Joe DoeJoe Doe

3866 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  which version of MySQL you are using?
  
  – vishal
  Mar 25 at 9:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @vishal MySQL 5.6 according to MariaDB version also I don't understand CTEs queries and WITH statements very good.
  
  – Joe Doe
  Mar 25 at 9:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have a PRIMARY KEY on each table. Read some of the Q&A at [greatest-n-per-group]. Implement some variant of what you find there. Then update the Question to show us how far you got.
  
  – Rick James
  Apr 17 at 3:07
  

  
  
  
  

add a comment | 

sqlfiddle: http://sqlfiddle.com/#!9/a209c7/2/0

my table is:

-- borrowed from https://stackoverflow.com/questions/55325155

CREATE TABLE IF NOT EXISTS `docs` (
 `id` int(6) unsigned NOT NULL,
 `section` int(2) unsigned NOT NULL,
 `rev` varchar(3) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `section`, `rev`) VALUES
 ('1', '1', 'a'),
 ('2', '1', 'b'),
 ('3', '2', 'c'),
 ('4', '3', 'd'),
 ('5', '3', 'e');

CREATE TABLE IF NOT EXISTS `docs_importance` (
 `doc_id` int(6) unsigned NOT NULL,
 `importance` int(3) unsigned NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs_importance` (`doc_id`, `importance`) VALUES
 ('1', '1'),
 ('1', '2'),
 ('1', '2'),
 ('2', '2'),
 ('2', '4'),
 ('3', '9'),
 ('4', '5'),
 ('5', '6');

and my query is:

-- based on answer https://stackoverflow.com/questions/55325155

SELECT t.id, t.section, t.rev, t.importance 
FROM (SELECT docs.id, docs.section, docs.rev, (SELECT SUM(docs_importance.importance) 
 FROM docs_importance 
 WHERE docs_importance.doc_id = docs.id) AS importance
 FROM docs
 GROUP BY docs.id
 ORDER BY importance DESC
 ) AS t
GROUP BY t.section;

---

what i am doing is to order the rows of docs by its calculated sum of importance then group it by its section to get the highest doc in importance for each section only

can this query be remade in a better way and faster?

mysql mariadb greatest-n-per-group query-performance

share|improve this question

edited Mar 25 at 9:39

Joe Doe

asked Mar 25 at 9:30

Joe DoeJoe Doe

3866 bronze badges

-- borrowed from https://stackoverflow.com/questions/55325155

CREATE TABLE IF NOT EXISTS `docs` (
 `id` int(6) unsigned NOT NULL,
 `section` int(2) unsigned NOT NULL,
 `rev` varchar(3) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `section`, `rev`) VALUES
 ('1', '1', 'a'),
 ('2', '1', 'b'),
 ('3', '2', 'c'),
 ('4', '3', 'd'),
 ('5', '3', 'e');

CREATE TABLE IF NOT EXISTS `docs_importance` (
 `doc_id` int(6) unsigned NOT NULL,
 `importance` int(3) unsigned NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `docs_importance` (`doc_id`, `importance`) VALUES
 ('1', '1'),
 ('1', '2'),
 ('1', '2'),
 ('2', '2'),
 ('2', '4'),
 ('3', '9'),
 ('4', '5'),
 ('5', '6');

-- based on answer https://stackoverflow.com/questions/55325155

SELECT t.id, t.section, t.rev, t.importance 
FROM (SELECT docs.id, docs.section, docs.rev, (SELECT SUM(docs_importance.importance) 
 FROM docs_importance 
 WHERE docs_importance.doc_id = docs.id) AS importance
 FROM docs
 GROUP BY docs.id
 ORDER BY importance DESC
 ) AS t
GROUP BY t.section;

share|improve this question

edited Mar 25 at 9:39

Joe Doe

asked Mar 25 at 9:30

Joe DoeJoe Doe

3866 bronze badges

share|improve this question

edited Mar 25 at 9:39

Joe Doe

asked Mar 25 at 9:30

Joe DoeJoe Doe

3866 bronze badges

asked Mar 25 at 9:30

Joe DoeJoe Doe

3866 bronze badges

asked Mar 25 at 9:30

Joe DoeJoe Doe

3866 bronze badges