SQL, Group by one column with one distinct column

31
February 12, 2019, at 12:00 PM

I have a table with following structure

id  phone_number  message    schedule_id
1 | 1212121212  | aaaaa |    12
2 | 2323232323  | bbbbb |    12
3 | 1212121212  | ccccc |    12
4 | 1212121212  | ddddd |    13

I want to fetch a row with same phone_number and different schedule_id I use the following query to fetch rows with same phone number

select * from message group by to_number having count(*)>1;

But it groups the rows with different schedule_id also

Can anybody help me to write the query ?

Answer 1

You can use EXISTS :

select m.*
from message m
where exists (select 1 from message m1 where m1.to_number = m.to_number and m1.id <> m.id);

You can also use GROUP BY, just include that column also :

select to_number, schedule_id 
from message 
group by to_number, schedule_id 
having count(*) > 1;
Answer 2

You should use a join for teh subqselect with count group by phone_number

  select m.* 
  from  message m
  inner join (
        select phone_number
        from message 
        group by phone_number
        having count(*)>1;
  ) t on t.phone_number  = m.phone_number
Answer 3

Is this what you are expecting?

select distinct phone_number, schedule_id from message;
READ ALSO
Get sum using DATE_FORMAT() and IN

Get sum using DATE_FORMAT() and IN

I've got a query that looks a little bit like this :

39
In select query include placeholder results where no direct result was found

In select query include placeholder results where no direct result was found

I have a table of race resultsNot all competitors enter every single race, but I would like to include a table which shows those races they didn't enter with blanks and a hyphen "-" value in the result column

41
mysql - current_timestamp and null

mysql - current_timestamp and null

I have question about mysql configurationI have the same database on AWS and local apache server on ubuntu

29