检索每组的最后一条记录并按位置排序 - MYSQL Retrive last record in each group and sort by location

MYSQL Retrive last record in each group and sort by location

这是我的表格

ID   Name   Qty   Location
1    B      1     Office
2    A      20    Office
3    A      50    Home
4    A      5     Office

这是我正在使用的SQL语句:

select t1.* from itemtable t1 
    left join itemtable t2 on (t1.name = t2.name and t1.id < t2.id) 
    where t2.id is null AND t1.location = 'office' 
    order by name ASC

在phpadmin中工作得很好，但是当我在我的页面上运行它时，它首先排序'id'。意思是我会得到B(因为id = 1)然后A

如果有帮助的话，这是我的代码

<table class="table table-hover">
    <thead>
        <tr>
        <th style="text-align:left">ID.</th>
            <th style="text-align:left">Item</th>
        <th style="text-align:center">Quantity</th>
        </tr>
    </thead>
    <tbody>
        <?php 
        $b = 0;
        $x = 1;
        while($row = $result->fetch_assoc()) {
            echo "<tr><td style='"text-align:left'">". $x. "</td><td style='"text-align:left'">". $row[name]. "</td><td style='"text-align:center'">". $row[qty]. "</td></tr>";
        $b = $b + 1;
        $x = $x + 1;
        }?>
    </tbody>
</table>

最终输出将是

ID   Name   Qty
1    B      1
2    A      75

如何使它按名称排序?由于

Select t1.* FROM  (SELECT * FROM itemtable ORDER BY ID DESC ) as t1 group by Location

检查一下:

select min(ID) as ID,Name,sum(qty) as Qty from itemtable group by location order by Name DESC