Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Lets say my database looks like this:

Table: messages

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 2  | 0        | 0        |  
| 2  | 1    | 0  | 1        | 2        |  
| 3  | 2    | 0  | 2        | 1        |  
| 4  | 2    | 1  | 0        | 0        |  
| 5  | 2    | 3  | 0        | 0        |  
| 6  | 2    | 0  | 1        | 3        |  
+----+------+----+----------+----------+  

Then in my selection from the table, I want to GROUP BY the following:

  • group rows where double_1 and double_2 is the same, while to = 0
  • group rows where from is the same, while to = $id and double_1 = 0 and double_2 = 0
  • group rows where to is the same, while from = $id and double_1 = 0 and double_2 = 0

Anyone have any ideas how to do that?

EDIT: As it seems that i did make some people misunderstanding this. I will now explain better. As I say the ways that I would like to GROUP BY, then when saying group rows where double_1 and double_2 is the same..., then its grouping the rows where double_1 and double_2 are the same. Like I have those two rows:

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 0  | 1        | 1        |  
| 2  | 1    | 0  | 2        | 2        |    
+----+------+----+----------+----------+    

They should not be grouped, as ID 1's double_1 AND double_2 is not the same as ID 2's double_1 AND double_2. While this would group:

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 0  | 1        | 2        |  
| 2  | 1    | 0  | 1        | 2        |    
+----+------+----+----------+----------+  

Im very sorry for the misunderstanding, hope that someone here are still willing to help me out.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

You can generate a flag column for each group that you need to get:

 CREATE OR REPLACE VIEW vw_messages AS
 SELECT id
      , `from`
      , `to`
      , double_1
      , double_2
      , case when double_1 = double_2 
              and `to` = 0
             then 1
        else 0
        end flag_1
      , case when double_1 = 0
              and double_2 = 0
              and `to` = $id
             then 1
        else 0
        end flag_2
      , case when double_1 = 0
              and double_2 = 0
              and `from` = $id
             then 1
        else 0
        end flag_3
 FROM messages

This way you can later get the rows that accomplish either one or any combinations of groups.

share|improve this answer
    
Thanks a lot, but when I try that. I get the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to, double_1 , double_2 , case when double_1 = double_2 ' at line 4 CREATE OR REPLACE VIEW vw_messages AS SELECT id , 'from' , to, double_1 , double_2 , case when double_1 = double_2 and to = 0 end flag_1 , case when double_1 = 0 and double_2 = 0 and to = 38 end flag_2 , case when double_1 = 0 and double_2 = 0 and 'from' = 38 end flag_3 FROM messages –  Mark Topper Apr 24 '13 at 11:50
1  
Sorry, I wrote the query straight from my brain compiler. It didn't warn me about the THEN clause missing... –  listik Apr 24 '13 at 13:10
    
Thanks a lot. I really need to look into the CASE clause, it seems useful. But sorry for the misunderstanding, as I actually wanted to do something else. I tried to explain it better in the question by editing it. Sorry and thanks a lot for your time. –  Mark Topper Apr 24 '13 at 20:31
    
Do not worry. I don't quite get what you want to get, though. If you do a group by using all columns but id, you would already get the rows grouped. If what you want is to get, separately, only the id's of rows that are "duplicated", you could create a view grouping the fields and adding the clause "HAVING COUNT(1)>1". Not sure if this is what you wanted; if that's the case, confirm it and I'll try to help! –  listik Apr 26 '13 at 7:19
    
Thanks a lot. Got it to work this way. –  Mark Topper Apr 26 '13 at 11:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.