Newbe trying sql

Walle

$q = mysql_real_escape_string ($_GET["q"]);

$sql="SELECT *, min(avd) as avd FROM handl
WHERE handl LIKE '%$q%'
GROUP BY handl
";
$result = mysql_query($sql);

handl | avd | overl
Jon----750--- Ola
Jon----474---Ann
Jon----568---Alina

Hi,

I have problem with this code. From table I want group "handl" and filter lowest "avd", this work but for some reason result from column "overl" prints out wrong, from another row. Something like this I need when filter lowest "avd" and grouped by "handl".

Joni----474-----Ann

Is it possible?

jeremyphphaven

you have a column named "handl" in a table named "handl"?

... FROM handl ...
this is line 1 of your sql, you may have to fix that

also add " order by avd asc " this will give you the lowest avd value first when you group by handl, this will probably fix your "overl" problem also (so be sure to remove your min(avd) statement.

all in all, check this sql statement out, run it and see if it fixes your problem:

$sql="SELECT * FROM `TABLENAME`
WHERE handl LIKE '%$q%' 
GROUP BY handl 
ORDER BY `avd` asc
";

Walle

Didn’t work, result didn’t show lowest value. Table name is “shandl”

Here is 2 sample with result that I’m trying to do but they don’t have WHERE and LIKE in there example code.

Picture 3 on http://www.tutorialspoint.com/mysql/mysql-min-function.htm

mysql> id, name, work_date, MIN(daily_typing_pages)
    -> FROM employee_tbl GROUP BY name;
+------+------+------------+-------------------------+
| id   | name | work_date  | MIN(daily_typing_pages) |
+------+------+------------+-------------------------+
|    3 | Jack | 2007-05-06 |                     100 |
|    4 | Jill | 2007-04-06 |                     220 |
|    1 | John | 2007-01-24 |                     250 |
|    2 | Ram  | 2007-05-27 |                     220 |
|    5 | Zara | 2007-06-06 |                     300 |
+------+------+------------+-------------------------+
5 rows in set (0.00 sec)

Picture 4 on http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;
+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| cherry | bing     |  2.55 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
+--------+----------+-------+

When I typing for example apple in a text field it remove all duplicate and only show one "apple row" that have lowest value. Result is exactly what I need but don’t know how or where to insert WHERE handl LIKE '%$q%'