complicated DB-Request

[thesilencer5]

Hi,

I have a DB table with that fields:

uniq, username, product, price, shopName

OK, I want to figure out all stored products and the ShopName where the product has the highest price, grouped by product, and restricted to one username.

I currently can only get the highest price of every product, but not the shop name.

I use:
$myQuery = "SELECT product, shopName, MAX(price) AS pmax FROM testtable WHERE username='$user' GROUP BY 'product'";

If I say: echo $row_query['pmax']; I get the highest price each product, but if I say: echo $row_query['shopName']; I get not the ShopName out of the same row as 'pmax'

Can anyone help me?

[paulnaj]

It might be just a typo ... you put '' around product in the GROUP BY clause.

$myQuery = "
SELECT product, shopName, MAX(price) AS pmax
FROM testtable
WHERE username='".$user."'
GROUP BY product
";
}

[paulnaj]

Just a thought ... you could try ...

$myQuery = "
SELECT product, shopName, MAX(price) AS pmax
FROM testtable
WHERE username='".$user."'
GROUP BY username
";
}

[thesilencer5]

Yes, that works (the second one). Thanks a lot🙂