Hide duplicate results in MySQL query

Question

I have a query in which I group results: SELECT brand, description, prodcode FROM table GROUP BY brand, prodcode Now the results are:

brand | description | prodcode
------------------------------
Brand1, Description1, Product1
Brand1, Description2, Product2
Brand1, Description3, Product4

What I would like to see is:

brand | description | prodcode
------------------------------
Brand1, Description1, Product1
        Description2, Product2
        Description3, Product4

Any ideas how to suppress this duplicate data before sending it to the application layer?

You can do it server side .. looping over the result .. (is not a mysql feature ) — ScaisEdge
– ScaisEdge, Commented Mar 8, 2017 at 19:40
It is possible to do using user variables and a case statement. you basically institute your own control break logic using the user variable and then use a case to see if the brand changes if it does, you display the new brand value otherwise, you display null. Though this type of logic is better suited to the App/Gui. — xQbert
– xQbert, Commented Mar 8, 2017 at 19:47

Amna Hashmi · Accepted Answer · 2017-03-08 19:46:26Z

2

You might want to consider using SELECT DISTINCT on the brand column to return only distinct values. For more information: https://www.w3schools.com/sql/sql_distinct.asp. However, collapsing the "Brand" column to only have one row but still be associated with three other rows in terms of "description" and "prodcode" is not possible in SQL.

answered Mar 8, 2017 at 19:46

Amna Hashmi

402 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Surfer Buddha Over a year ago

Using distinct in the above case doesn't change the results whatsoever.

xQbert · Accepted Answer · 2017-03-08 20:16:43Z

There's always a way. The question is "Should we do this". Note by doing this you hide the brand data. If the data is reordered, sorted, the sorter loses the ability to know which brand is for what row. If this is just a printed report that is not to be resorted or expected to a PDF where a user can not manipulate the data then ok.. But if this is so a user can dump to excel and manipulate later... better to have all the data.

I personally find this display of information distasteful on electronic data, but ok on printed forms or static reports. The reason: on electronic I am able to import into excel sort and play with the data; but if columns are "missing" redundant data then electronic manipulation becomes problematic.

Working SQLfiddle

CREATE Table foo (
 brand varchar(20),
 description varchar(20),
 prodcode varchar(20));


Insert into foo values ('Brand1', 'Description1', 'Product1'),
('Brand1', 'Description2', 'Product2'),
('Brand1', 'Description3', 'Product4'),
('Brand2', 'Description3', 'Product4'),
('Brand2', 'Description3', 'Product4'),
('Brand1', 'Description3', 'Product3');

QUERY:

SELECT case when@var <> f.brand then @Var :=brand end as Brand
     , f.description
     , f.prodcode
FROM (SELECT * FROM foo ORDER BY brand, description, prodcode) f
CROSS JOIN  (SELECT @var := '') b

RESULTS:

Brand   description     prodcode
Brand1  Description1    Product1
(null)  Description2    Product2
(null)  Description3    Product3
(null)  Description3    Product4
Brand2  Description3    Product4
(null)  Description3    Product4

Why this works:

The magic happens in the cross join and on the case evaluation.

There's 1 table. We're sorting the records before we join. We're creating a user variable called @var and setting it to '' on the first record in in table foo. Then we assign @var to the brand of the first record. When the SQL cross joins to the 2nd filed @var is now the brand of the first record. If the brands match, no record is displayed, if the brands don't match, we change the value of @var and display the new value.

Risks:

we if other table joins occur, or the order is not as desired in the table, we would need to do a subquery first on "foo" to order by brand
applying any order by to this which involves brand1 is now worthless
It's not as re-usable. Another module which may want to leverage the data (view) is no longer able to do so because brand has been hidden on some of the records.

Collectives™ on Stack Overflow

Hide duplicate results in MySQL query

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related