Select Distinct field

[cjackson111]

Hello.

 

I am trying to display only one instance of records that have the same memberid in my db. I am using the following statement but it continues to show all of the records that have the same memberid. Any ideas what I may be doing wrong?

 

$sql = "select DISTINCT memberid, event, category, date, enddate, locality, location, address, city, state, zip, contact, phone, notes, doc1, doc2, doc3, doc4, doc5 from event where date >= '$datenow' ORDER by date ASC";

 

Thanks for any help!

[Eiolon]

Do you mean you are trying to see all records that belong to a particular memberid?

[cjackson111]

No, I only want to show one record that has the same memberid. For instance, I have 5 similar records that all have the same memberid. I only want to show one of them

[Eiolon]

All, then use GROUP BY.

 

When you use distinct, if ANY of the other fields are different for that one memberid, it is considered distinct.

 

For example, I have two number 1 id's but they have different names.  therefore they are both listed.

 

mysql> SELECT DISTINCT id, name FROM test;
+----+-------+
| id | name  |
+----+-------+
|  1 | cat   |
|  1 | dog   |
|  2 | bird  |
|  3 | horse |
+----+-------+

 

So I use group and here is the outcome:

 

mysql> SELECT id, name FROM test GROUP BY id;
+----+-------+
| id | name  |
+----+-------+
|  1 | cat   |
|  2 | bird  |
|  3 | horse |
+----+-------+
3 rows in set (0.00 sec)

[PFMaBiSmAd]

I only want to show one of them

 

Yes, but which one? The first one in the table, last one in the table, oldest one, newest one, longest one, shortest one, average of them, sum of them?

 

Computers need to be told exactly what to do.

[cjackson111]

Thanks, that worked! I was thinking I needed to use DISTINCT. I specified to display the first record of each group by adding an order by clause.

 

Thanks for your help!!!

[cjackson111]

Ok, I have a little snag. Everything works great except for the last bit (order by date ASC). It still shows whatever record is first in the database. Any ideas what I may be doing wrong?

 

$sql = "select memberid, event, category, date, enddate, locality, location, address, city, state, zip, contact, phone, notes, doc1, doc2, doc3, doc4, doc5 from event where date >= '$datenow' GROUP BY memberid ORDER BY date ASC";

 

Thanks!

[PFMaBiSmAd]

To get a specific row per group, see this link - http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html