CODEWITHSUNDEEP
mysql
Muhunthan
Muhunthan

Reputation: 413

DISTINCT gives duplicate results

I'm trying to get the latest row for each msisdn which I'm passing to IN clause

This is my table structure

CREATE TABLE `cell_volume_details` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `date_time` bigint(20) DEFAULT NULL,
  `cell_id` int(11) DEFAULT NULL,
  `volume` bigint(20) DEFAULT NULL,
  `dn` int(11) DEFAULT NULL,
  `cg_key` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO CHARSET=latin1;

This is my query 

SELECT DISTINCT dn, cell_id as cell, cg_key 
        FROM cell_volume_details WHERE dn IN    
        (55484522680, 55484522794, 55484522289, 
         55484522995, 55484522840, 55484522106,
         55484522610) ORDER BY id DESC;

This is the result

+-------------+-------+--------+
| dn          | cell  | cg_key |
+-------------+-------+--------+
| 55484522106 |  4181 | cg1    |
| 55484522106 | 21183 | cg1    |
| 55484522840 | 17369 | cg11   |
| 55484522289 |  1222 | cg2    |
| 55484522106 | 32181 | cg1    |
| 55484522106 | 32183 | cg1    |
| 55484522794 | 27363 | cg5    |
| 55484522995 | 24219 | cg3    |
| 55484522794 | 21368 | cg5    |
| 55484522794 | 40367 | cg5    |
| 55484522794 | 10367 | cg5    |
| 55484522289 |  1229 | cg2    |
| 55484522289 | 17228 | cg2    |
| 55484522289 |  1227 | cg2    |
| 55484522794 | 45368 | cg5    |
| 55484522610 |  6018 | cg9    |
| 55484522680 |  2373 | cg2    |
| 55484522610 |  1158 | cg9    |
| 55484522610 |  3151 | cg9    |
| 55484522794 | 13369 | cg5    |
| 55484522610 |  3153 | cg9    |
| 55484522289 |  1228 | cg2    |
| 55484522840 | 17369 | cg2    |
| 55484522610 | 14163 | cg9    |
| 55484522610 | 14169 | cg9    |
| 55484522794 |  8361 | cg5    |
| 55484522680 | 17373 | cg2    |
| 55484522794 |  8362 | cg5    |
| 55484522289 |  1225 | cg2    |
| 55484522794 | 10368 | cg5    |
| 55484522794 |  8367 | cg5    |
| 55484522680 | 17371 | cg2    |
| 55484522680 | 27362 | cg2    |
| 55484522995 | 24217 | cg3    |
| 55484522794 | 43369 | cg5    |
| 55484522794 | 10365 | cg5    |
| 55484522840 | 17369 | cg13   |
| 55484522610 |  1152 | cg9    |
| 55484522794 |  8368 | cg5    |
| 55484522995 | 24218 | cg3    |
+-----------+-------+--------+

I couldn't find what was the wrong.I have 80 Million record in the table and it's get very slow when I do the GROUP BY, Anyone please help me

Upvotes: 0

Views: 69

Answers (2)

P.Salmon
P.Salmon

Reputation: 17665

select * 
from 
(
select s.*,
        if(s.dn <> @p,@rn:=1,@rn:=@rn+1) rn,
        @p:=s.dn
from
(
SELECT id,dn, cell_id as cell, cg_key 
FROM cell_volume_details 
WHERE dn IN    
            (55484522680, 55484522794, 55484522289, 
         55484522995, 55484522840, 55484522106,
         55484522610) 
) s 
, (select @rn:=0 ,@p:=0) rn 
order by s.dn,s.id desc
) t 
where t.rn = 1

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133410

If you want the last row for dn you should select the rows with max(id) group by cell_volume_details

select dn, cell_id as cell, cg_key 
FROM cell_volume_details
where id in (
        select max(id) from cell_volume_details WHERE dn IN    
        (55484522680, 55484522794, 55484522289, 
         55484522995, 55484522840, 55484522106,
         55484522610) 
  )

Upvotes: 1

Related Questions