How to remove duplicates row?

[Walle]

$sql="SELECT DISTINCT name, colnum
FROM col
WHERE name
LIKE '%$q%' 
LIMIT 5";
$result = mysql_query($sql);

Hi,

I try to remove duplicates row/value with DISTINCT but it not work. It work only if I remove , colnum after name .

What’s wrong with this?

[halojoy]

First are you sure
'colnum' is a correct field in your table 'col' ?

If everything is alright, I can not see anything wrong with your query.
It should really work with DISTINCT name, colnum

I am not 100% sure,
but maybe if you change and try this:

<?php

$sql = "SELECT colnum, DISTINCT name
FROM col
WHERE name
LIKE '%$q%' 
LIMIT 5";

$result = mysql_query($sql);

?>

[Walle]

I tested with $sql = "SELECT colnum, DISTINCT name
but got Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in on line 25

Line 25
while($row = mysql_fetch_array($result))

name and colnum is correct field. As soon i add more than one columns after DISTINCT it want work and show duplicates results.

[Bjom]

are you sure those are duplicates? distinct will fetch rows that are unique in the sense that the combination of all fields given are unique. It works much like group by - see this link

E.g.:
select distinct name, colnum

name, colnum
mary | 1
mary | 2

those are distinct.
another

mary | 1

would be a duplicate.

Give us a bit more info about both the table and the result you want to obtain and we can figure something out, I would guess it will most likely turn out to be something with a subselect...

Bjom

[Walle]

$sql="SELECT * FROM col
WHERE name
LIKE '%$q%' 
GROUP BY name
LIMIT 5
";
$result = mysql_query($sql);

Got this work nice with GROUP BY but have some sort problem. If table is like this

name------colnum
A-----------2
A-----------1
A-----------2

I want sort and remove duplicate like this

name-----colnum
A----------1

Sorted by lowest value from colnum, is it possible?

[Bjom]

not sure which one you want to remove. one of the following should achieve what you like:

SELECT name, MAX(colnum) as cn FROM mytable group by name

or

SELECT name, MIN(colnum) as cn FROM mytable group by name

[Walle]

Not work and got error
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in on line 22

Line 22 is line with while($row = mysql_fetch_array($result))

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

$sql="SELECT name, MIN(colnum) as cn FROM col group by name
WHERE name
LIKE '%$q%' 
LIMIT 5
";
$result = mysql_query($sql);

echo "<table border='1' width='100%'>
<tr>
<th>Name</th>
<th>Colnum</th>
</tr>
";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['colnum'] . "</td>";
echo "</tr>";
}
echo "</table>";

[Bjom]

SELECT name, MIN(colnum) as cn FROM col group by name
    HAVING name
    LIKE '%$q%' 

try this and read up on group by. Group by and where dont mix well

The error message means, that the query did not work out, that why you received a boolean value "false", that is now stored in your variable $result.

[ixalmida]

You need to add an index column if there isn't one already or you will make things unnecessarily complicated. You can always remove the index later if you need to.

ALTER TABLE cols ADD COLUMN myindex INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

Now you can delete your duplicates easily...

DELETE FROM cols WHERE myindex NOT IN (SELECT MIN(myindex) FROM cols GROUP BY name, colnum)

Sorting is not something you can do from within a table. In order to re-sort a table, you have to dump it into a new table and then you can dump it back with a sort condition.

CREATE TABLE cols_temp SELECT FROM cols;
DELETE FROM cols;
INSERT INTO cols SELECT FROM cols_temp ORDER BY name, colnum;

[Bjom]

That will leave whatever entry is the "first" in the table, deleting the rest. If that is the desired outcome, yes it is possible by adding a column like that. Doesnt seem like the OPs intention though.

Of what use is a "sorted" table? There are queries and views for a sorted representation of data in a table. Since new data will be simply dumped to the table anyway....what benefit do I get from doing this?

[ixalmida]

I'm just answering the question that I perceived was being asked. Perhaps I misinterpreted it. I guess we need to let the questioner decide.

[Walle]

SELECT name, MIN(colnum) as cn FROM col group by name
    HAVING name
    LIKE '%$q%'

Didn’t work, got same messages on same row.

About delete, don’t want remove anything just not show/print duplicates from name. This work with GROUP BY name. What I want is, it prints after lowest value in colnum. As I understand GROUP BY name selects first, value "A" it founds in table if table looks like this:

name------colnum
B-----------5
B-----------3
A-----------7
A-----------4
A-----------8

output will be like this I think:

name-----colnum
A----------7

but want have output like this with lowest value "4".

name-----colnum
A----------4

[Bjom]

it does work and it works exactly as intended (i checked).

most likely you pass an invalid value inside the variable $q.

Try this before executing the query and scrutinize the output:

echo $sql;

[Walle]

$sql="SELECT *, min(colnum) as cn FROM col GROUP BY name
    HAVING name
    LIKE '%$q%'
";

This code work now but changed it little bit so it select and print out from all columns but there is some sort problem. It should get lowest value "1" but for some reason not work. Example table

name----------------------colnum
Mike Johnson 50K-----------3
Mike Johnson 50K-----------2
Mike Johnson 50K-----------1
Mike Johnson 80K ----------2
Mike Johnson 80K-----------1
Lisa Johnson 50K-----------3
Lisa Johnson 50K-----------2
Lisa Johnson 50K-----------1
Lisa Johnson 80K-----------2
Lisa Johnson 80K-----------1

output is like this (when typing in a text field "Johnson"):

 
Lisa Johnson 80K-----------2
Lisa Johnson 50K-----------3
Mike Johnson 80K----------2
Mike Johnson 50K----------3

but should be like this

Mike Johnson 50K----------1
Mike Johnson 80K----------1
Lisa Johnson 50K-----------1
Lisa Johnson 80K-----------1

[Walle]

Bjom;10945889 wrote:

it does work and it works exactly as intended (i checked).

most likely you pass an invalid value inside the variable $q.

Try this before executing the query and scrutinize the output:

echo $sql;

It work perfect now, problem seems was because VarChar. I changed to int instead.

Also I changed $row['colnum'] to $row['cn']. At first I never understood that "cn" from min(colnum) as cn .

Anyway it seems work now and thanks for your help. Thanks also to you other of course.