CODEWITHSUNDEEP
phpmysql
user3128717
user3128717

Reputation: 27

how to write a query to suppress consecutive repeating values in resultset columns?

I need to make repeated values be empty or null under Column Name (TGroup_Name),when selecting Table

For Example:

In below table Column Name(TGroup_Name) having repeated values (ED_1) in two rows. So i want to make null or empty second row value(ED_1).But should not affect entire row. 


TGID    TParent_Group_ID    TGroup_Name TGroup_Type TEID    TEmp_Group_ID   TEmp_Name   TEmp_Type
1              0                MD            G      10           1           Mallik        GH
10             1                ED_1          G       5          10           Thevan        GH
10             1                ED_1          G       7          10           Gupta          U
11             1                ED_2          G      11          11           Dinesh        GH

Upvotes: 1

Views: 526

Answers (1)

O. Jones
O. Jones

Reputation: 108796

Most developers think it's best to suppress such consecutive repeating values in their result sets using application-side procedural code (like Java or PHP).

But you can do it in the MySQL variant of SQL like this (http://sqlfiddle.com/#!2/691f5/4/0).

SELECT TGID,
       TParent,
       IF(Group_ID=@PREV,'',@PREV:=Group_ID) AS Group_ID,
       TGroup_Name
  FROM TABLE1,
       (SELECT @PREV:='xxxx') AS i
 ORDER BY TABLE1.TGID

The IF combined with the running value of @prev suppresses consecutive duplicates. The extra (SELECT...) subquery serves to initialize @prev.

This is a tricky business to get right. For example, if you just say ORDER BY TGID instead of what I have shown it will come out wrong. It's also entirely nonportable to other makes and models of table server.

Upvotes: 1

Related Questions