Select one row without duplicate entries

Question

In mysql table info i have :

Id , Name , City , date , status

I want to select all names from "info" Making the query

$query = mysql_query("SELECT name FROM info WHERE status = 1 ORDER BY id") 
         or die(mysql_error());

while ($raw = mysql_fetch_array($query)) 
{
  $name = $raw["name"];
  echo ''.$name.'<br>';
}

Well, the result is that it returns all the entries. I want to echo all the entries without duplicates.

Saying: under raw "name" we have inserted the name "John" 10 times.
I want to echo only one time. Is this possible?

score 51 · Accepted Answer · edited Jun 27 '18 at 09:56

51

It's pretty simple:

SELECT DISTINCT name FROM info WHERE status = 1 ORDER BY id

The SQL keyword DISTINCT does the trick.

edited Jun 27 '18 at 09:56

Norielle Cruz

170
5
14

answered Aug 12 '11 at 13:31

Bohemian

412,405
93
575
722

Dalen · Answer 2 · 2011-08-12T13:37:09.093

try using this as your query:

SELECT DISTINCT name FROM info WHERE status = 1 ORDER BY id

to get distinct names

or as other suggested use GROUP BY

SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id

I think the first one is more intuitive and there are no big performance difference between the two

EDIT

as the OP wants also the number of names here we go:

SELECT name,COUNT(id) AS n_names
FROM info WHERE status = 1
GROUP BY name
ORDER BY name

you can ORDER BY name or n_names depending on what you need

cristian · Answer 3 · 2011-08-12T13:37:45.717

6

Change

SELECT name FROM info WHERE status = 1 ORDER BY id

to

SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id

Observe that GROUP BY was added. More about group by http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Edit:
for name with number of apparences try

SELECT name, count(name) FROM info WHERE status = 1 GROUP BY name ORDER BY id

edited Aug 12 '11 at 13:37

answered Aug 12 '11 at 13:19

cristian

8,676
3
38
44

works , but is i want to pun after the "name" the number of how many entryes are like this `code` echo ''.$name.' (times)'; – Darius Aug 12 '11 at 13:24

score 1 · Answer 4 · answered Aug 12 '11 at 13:18

1

add GROUP BY name to your SQL Statment - this will only bring back one of each entry from the name column

answered Aug 12 '11 at 13:18

Manse

37,765
10
83
108

When the GROUP By name is applied does it only take the first row? if the order by ID? – Mar 20 '17 at 22:42
1

@YumYumYum the order is not guaranteed unless you add the `order by` statement – Manse Mar 21 '17 at 13:48
Thank therefore for safety i used this `SELECT id,duplicate_name,duplicate_email from SERVICE where duplicate_email='a@b.com' GROUP BY duplicate_email ORDER BY id`, which gives me the row when that user already signed up and next time login again on that particular project – Mar 21 '17 at 20:47

score 0 · Answer 5 · answered Feb 27 '13 at 10:32

This works for me, returns the table names for a given database.

my $sql="select distinct table_name from COLUMNS  where table_schema='$database'"
my $sth = $dbht->prepare( $sql )
      or die "Cannot prepare SQL statement: $DBI::errstr\n";
  $sth->execute
      or die "Cannot execute SQL statement: $DBI::errstr\n";

  if ($DBI::err){

    $msg= "Data fetching terminated early by error: $DBI::errstr";

}


while (@col=$sth->fetchrow_array()){
    $table[$i]=$col[0];
    $i++;
}

score 0 · Answer 6 · edited Mar 20 '17 at 22:40

0

Let us say that you are sending bulk sms and you don't want to send the same message two times to the same guy John . What i discovered , is that using the trick of GROUP BY and ORDER BY at the same time works perfectly . But I don't say that its the best way . Here is how u can use it

SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY name

EDIT: This is important to note, when you need more then single column and there values are unique per row then the DISTINCT was not helping.

edited Mar 20 '17 at 22:40

answered Nov 20 '16 at 17:52

Humphrey

2,659
3
28
38

1

+1 this is CORRECT in my-case. Because i had to take `SELECT id,name,email from SIGNUP where groups='admin' Group by email ORDER by id` – Mar 20 '17 at 22:39

score 0 · Answer 7 · answered Jun 15 '17 at 00:44

0

$sql="SELECT DISTINCT name FROM status =1 GROUP BY name ORDER BY name";

$query = mysqli_query($conn,$sql);
<?php while ( $fire=mysqli_fetch_array($query)) { ?>
<h4><?php echo $query['name']; ?><br></h4>
<?php } ?>

answered Jun 15 '17 at 00:44

Laud Randy Amofah

29
2

score 0 · Answer 8 · answered Aug 12 '11 at 13:19

use GROUP BY name statement

$query = mysql_query("SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id") or      die(mysql_error());

while ($raw = mysql_fetch_array($query)) {
                $name = $raw["name"];
                echo ''.$name.'<br>';
                }

Select one row without duplicate entries

8 Answers8

Linked

Related