MySql SELECT get 1 record from multiple record

Question

I have a table which looks something like :

id name qty price rev

1 book1 2 $10 0

2 book2 1 $5 0

3 book1 3 $15 1

4 book3 2 $10 0

5 book4 3 $15 0

6 book2 3 $15 1

7 book2 4 $20 2

and the data that will be displayed on the web is,

no name qty price rev

1 book1 3 $15 1

2 book2 4 $20 2

3 book3 2 $10 0

4 book4 3 $15 0

This is the query that I use

<?php
$sql = mysql_query("SELECT SUM(qty*price) FROM data_ph_user ORDER BY id");
while($res = mysql_fetch_array($sql)){
?>
<!-- display result -->
<?php 
$resf1 = $res['SUM(qty*price)']; if($resf1 == 0){ echo '0'; }else{ echo $resf1; }
?>
<!-- end display -->
<?php } ?>

so, now how to get the data?

I've done various kinds of queries by using 'WHERE' but do not get results

Answer 1

I got your point.. so you just want to select the line where qty is maximum per book name.. right??

try the below query

select
  name, qty, price, rev
from
  MyTestingTable
where
  qty = (select max(qty) from MyTestingTable i where i.Name = MyTestingTable.Name)
order by MyTestingTable.name

you can have a look on given SQL fiddle as well.. it will give you the desired result..

http://sqlfiddle.com/#!2/9162c4/8

Answer 2

The reason is that SUM is an aggregate function. Such functions normally return one row: in this case, you would sum over the entire table unless you group by something.

If I understand your question correctly, you wish to list the highest revision of all elements?

SELECT *, MAX(rev) FROM data_ph_user GROUP BY `name`

Then it's only a matter of typesetting it correctly.