Duplicate records SQL Query

Is there a way to use the distinct clause on one field, but get other fields?

Like I have a db and it has a lsit of businesses but there are multiple records for a lot of businesses , extra listings for extra phone numbers.

I want to get the unique business names, and the contact info from one of the records, but disregrard the secondary records,

I know I could use distinct but I also need the primary key feild, so using distinct doesn't work,

Is there way to do that with sql? I can probalby jsut write something to check in php , but it seems just writing the sql query properly would be much better

 

perhaps using the "group by" clause?

here's an example of how to use "group by".... perhaps this'll help ya sort it out.

for instance:

if you run the following sql query and get the following results:

mysql> SELECT poet, anthology, copies_in_stock FROM writer;

| Mongane Wally Serote | Tstetlo | 3 |
| Mongane Wally Serote | No Baby Must Weep |8 |
| Mongane Wally Serote | A Tough Tale | 2 |
| Douglas Livingstone | The Skull in the Mud | 21 |
| Douglas Livingstone | A Littoral Zone | 2 |



then running the following sql query with "GROUP BY" will yield these results:

mysql> SELECT poet, anthology, copies_in_stock FROM writer GROUP BY poet;

| Douglas Livingstone | The Skull in the Mud | 21 |
| Mongane Wally Serote | Tstetlo | 3 |

for reference, here's a link to the page i pulled this example from - it has other variations and uses for "Group by".

http://www.databasejournal.com/features/mysql/article.php/3469351


HTH

VG

 

Using Group by can also sort out the problem. YOu can make views to make your query distinct with respect to one primary field. This will yeild result with no duplication.

 

~~ cough... cough.... ~~

someone should dust these old posts every few months. LOL

j/k

vectorgraphx