2

Given a table like this:

id     number     anotherNumber
1      1          10
2      1          20
3      2          20
4      2          10
5      3          10

If I run the query:

Select *, GROUP_CONCAT(number)
FROM docs
GROUP BY number

I will get:

id  number  anotherNumber   GROUP_CONCAT(number)
1   1       10              1,1
3   2       20              2,2
5   3       10              3

see sql fiddle

However I want to get:

id  number  anotherNumber   GROUP_CONCAT(number)
1   1       20              1,1
3   2       20              2,2
5   3       10              3

Basically I want the numbers in the anotherNumber column to be sorted in DSEC order - it should always be the highest one.

I know you can put an ORDER BYin the GROUP_CONCAT but this will only affect the concatenated values, not the "merged ones". So is there a simple way?

Björn
  • 12,587
  • 12
  • 51
  • 70
  • 2
    It is strange to be aggregating the group by column. Why do you want to do this? – Tim Biegeleisen Mar 24 '18 at 15:09
  • Agree with @TimBiegeleisen. The result will always be `number` repeated `COUNT(*)` times. What's the point, except to discover the number of records in each group in a very roundabout way? – eggyal Mar 24 '18 at 16:29
  • 1
    IMHO `COUNT(*)` would be more appropriate than `GROUP_CONCAT(number)`. Same information with less data. – Paul Spiegel Mar 24 '18 at 16:29
  • @tim agree, but this is a very simplified example focusing on the isssue. in my real life case i am joining tables, etc. and the values in the concat group are not duplicate. – Björn Mar 24 '18 at 18:12

2 Answers2

2

You could use:

Select MIN(id) AS ID, number, MAX(anotherNumber) AS anotherNumber,
       GROUP_CONCAT(number)
FROM docs
GROUP BY number;

SQLFiddle Demo

You should always wrap columns that are not specified in GROUP BY with aggregate function(unless they are functionally dependent on GROUP BY columns and your RDBMS supports ANSI-99 Optional feature T301, Functional dependencies)

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • ...unless they are functionally dependent on the `number` column. – Tim Biegeleisen Mar 24 '18 at 15:10
  • That is the ANSI standard (except for Oracle). – Tim Biegeleisen Mar 24 '18 at 15:11
  • @TimBiegeleisen SQL Server also does not allow columns that are not in `GROUP BY` – Lukasz Szozda Mar 24 '18 at 15:12
  • Oh yes it does. If I group by a primary key column I may select _any_ column in that table, because they are functionally dependent. Only Oracle doesn't follow this, among the major databases, because it's really old school. – Tim Biegeleisen Mar 24 '18 at 15:14
  • @TimBiegeleisen Are you sure? [Demo](http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=401cae2856f47b0d4dc9c21c71694dce) or I am missing something obvious `SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns` and [T301, Functional dependencies](https://msdn.microsoft.com/en-us/library/hh544322(v=sql.105).aspx) – Lukasz Szozda Mar 24 '18 at 15:16
  • @TimBiegeleisen I would be grateful if you could provide demo for SQL Server – Lukasz Szozda Mar 24 '18 at 15:22
  • @TimBiegeleisen What matters more than SQL Server or Oracle is MariaDB, which doesn't support it. And they seem not even to plan it :-( – Paul Spiegel Mar 24 '18 at 16:25
  • It looks like [you are right about SQL Server](http://rextester.com/XVRU93057) but Postgres and MySQL do support this part of the ANSI standard. I upvoted you, by the way, but your language `always` wrap is not necessarily true on every database. – Tim Biegeleisen Mar 25 '18 at 04:22
  • @TimBiegeleisen Sure, I've added note about it – Lukasz Szozda Mar 25 '18 at 08:32
0

The following code is the easy way to get the desired result:

SELECT id, number, MAX(anotherNumber) AS anotherNumber, GROUP_CONCAT(number)
FROM docs
GROUP BY number;

I have also attached my SQLFiddle.

You can check it. Good Luck!

Vignesh VS
  • 921
  • 1
  • 14
  • 30