Question: I have a SQL statement:

```
$sql = "SELECT speed, distance from result where id='".$ids."'";

$b = mysqli_query($con, $sql);

```

i want to show all speed and distance records of this user, which has been done by using 

```
if ($b->num_rows >0){
  while ($row = $b->fetch_assoc()){
    echo "speed: " . $row["speed"]. " - Distance: " . $row["distance"]. "<br>";
  }
}

```

But my purpose is to store all records into an array, and then use json to send them to my android phone. just like :

```
$res = mysqli_fetch_array($r);

$result = array();

array_push($result, array(
"totalTime"=>$res['totalTime'],
"totalDis"=>$res['totalDis']
));

echo json_encode(array("result"=>$result));

```

i want to know how to do this with the records of speed and distance.

Answer: 
As per my understanding, in the example you provided, you want to keep the record with `ID` = 5 and delete the rest.

```
WITH CTE AS(
   SELECT ID, ACCNO, ACCNAME, ADDRESS1, ADDRESS2, CITY,
       RN = ROW_NUMBER()OVER(PARTITION BY ACCNO ORDER BY ID DESC)
  FROM SUPPLIERS
)
DELETE FROM CTE WHERE RN > 1

```

That should do the trick!
this worked for me.

**Table**

```
ID   ACCNO       ACCNAME         ADDRESS1        ADDRESS2        City
1    1001        Joe B Ltd       123 Street1                     London
2    1001        JoeB Ltd        123 Street1                     London
3    1001        JoeB Ltd        123 Street1                     London
4    1001        JoeB Ltd        123 Street1     London          London
5    1001        JoeB Ltd        129 Street9                     London
6    67          Nise            Gata1      
7    67          Nisse           Gata2      
8    67          Nisse           Gata1           Haninge         Stockholm

```

**RESULT**:

```
ACCNO    ACCNAME         ADDRESS1        ADDRESS2        City
1001     JoeB Ltd        123 Street1     London          London
67       Nisse           Gata1           Haninge         Stockholm

```

**Code**:

```
select distinct 
    [ ACCNO  ],
    FIRST_VALUE([ ACCNAME    ]) OVER (PARTITION BY [ ACCNO  ] ORDER BY case when [ ACCNAME    ] is null then 1 else 0 end, rownumber  ) as [ ACCNAME    ],
    FIRST_VALUE([ ADDRESS1     ]) OVER (PARTITION BY [ ACCNO  ] ORDER BY case when [ ADDRESS1     ] is null then 1 else 0 end, rownumber  ) as [ ADDRESS1     ],
    FIRST_VALUE([ ADDRESS2    ]) OVER (PARTITION BY [ ACCNO  ] ORDER BY case when [ ADDRESS2    ] is null then 1 else 0 end, rownumber  ) as [ ADDRESS2    ],
    FIRST_VALUE([ City]) OVER (PARTITION BY [ ACCNO  ] ORDER BY case when [ City] is null then 1 else 0 end, rownumber  ) as [ City]
FROM 
(
    SELECT 
          [ ACCNO  ]
          ,[ ACCNAME    ]
          ,[ ADDRESS1     ]
          ,case when ltrim(rtrim([ ADDRESS2    ] )) = '' then null else [ ADDRESS2    ] end as [ ADDRESS2    ] -- spaces = NULL
          ,case when ltrim(rtrim([ City] )) = '' then null else [ City] end as [ City]

          ,count(*) as quantity
          ,ROW_NUMBER() OVER (
                                PARTITION BY [ ACCNO  ] 
                                ORDER BY 
                                    [ ACCNO  ], 
                                    count(*) desc
                             ) as rownumber
      FROM [dbo].[test_sql]  
      GROUP BY cube ( [ ACCNO  ]
          ,[ ACCNAME    ]
          ,[ ADDRESS1     ]
          ,[ ADDRESS2    ]
          ,[ City])
        HAVING [ ACCNO  ] is not null 
 ) myGroup

```