Distinct?

Masna

Hello everyone! First, I want to destroy the misleading I've made, by saying that this isn't really a PHP question, but a MySQL question (but answering this will help me with a HUGE PHP project!), but I trust you guys as you've been a huge help before, so I figured I come here!

Anyway... I need help with DISTINCT. When queries are simple, like, SELECT DISTINCT field FROM table, I understand. That will simply select only unique values of field field. But, what about when it gets more complicated, like... SELECT DISTINCT * FROM table? What will that do? Also, what if I wanted to select one distinct field, but the other non distinct information?

Thanks in advance everyone. Just keep in mind, helping me with this would mean so much to me, you couldn't understand.

Weedpacket

Moving this to a more appropriate forum. (At least, more appropriate than where it started from. A MySQL forum - if there are such things - would be even more appropriate, but I can't move it there.)

Oh, and incidentally, every record returned by select distinct * would be different from every other record in at least one field. Try it.

Masna

Weedpacket wrote:
Moving this to a more appropriate forum. (At least, more appropriate than where it started from. A MySQL forum - if there are such things - would be even more appropriate, but I can't move it there.)

Oh, and incidentally, every record returned by select distinct * would be different from every other record in at least one field. Try it.

I see. So, if there were two rows in a table, only one would be returned if every field of both rows had all the exact same values. Would you mind giving my second question a go? How could I select a few DISTINCT fields, and with the same query, select other fields regularly (voiding the DISTINCT initiative)?

Iain

I'm still new to PHP so might not be quite right here - but what I think DISTINCT can do is this :

If you have a query that looks up two (or presumably more) tables, eg where 'Customers' may have many products, you might want a results page of all the customers that had bought Product A.

But if they'd bought other products as well, the query without the DISTINCT might look like :

customer1, product1
customer1, product2
customer1, product3
customer2, product2
customer2, product4
etc

So in your results page you might not want duplicates of customers - but maybe just one result for each customer with the matching product - you could then click through to a details page that listed the products for the selected customer.

Hope that makes some sense - and hopefully someone will give a more detailed answer too.

Iain

Weedpacket

Masna wrote:
How could I select a few DISTINCT fields, and with the same query, select other fields regularly (voiding the DISTINCT initiative)?

Which other fields? I'll use Iain's example records (since PHP knowledge is irrelevant here):

Iain wrote:
customer1, product1
customer1, product2
customer1, product3
customer2, product2
customer2, product4
etc.

Now let's say you want to retrieve DISTINCT customers, but "voiding the DISTINCT initiative" for the products. The results would be

customer1, ....what goes here?....
customer2, ....(exercise for the reader)....
etc.

Masna

Ehhh, I'm still confused 🙁. Would you mind putting that in a query? You have the right idea as to what I mean. I'll give an example to make it easier on you...

Let's say we have three fields: address, name, age. Would you mind creating a query in which distinct values for address and name are chosen, but not age (nevertheless age is retrieved)?

Thank you!

ahundiak

DISTINCT always operates on all the columns being fetched. Depending on exactly what it is that you are trying to do, I suspect that the GROUP BY clause is what you are looking for.

Masna

Not exactly. I'm trying to retrieve distinct values for some fields, and at the same time, retrieve everything else. It's tough to explain, but I need this for a MySQL Admin I'm creating. You can check it out at http://mysqlquickadmin.com/. I want to incorporate DISTINCT to SELECT queries, but I need to grab all other values of other fields so that users can edit/view/delete rows.

Meh... anyone?

ahundiak

Assume you have a table with name, city, age fields with data:
Smith Huntsville 23
Smith Huntsville 28
Smith Huntsville 30
Jones Huntsville 17
Jones Huntsville 18

What exactly would you like this distinct query to return?

Masna

I want distinct name values, but also the other values along with those distinct name values.

Weedpacket

Like I asked (and you didn't answer), which other values?

Smith, Huntsville, 23
Smith, Huntsville, 28
Smith, Huntsville, 30
Jones, Huntsville, 17
Jones, Huntsville, 18

If you select Distinct names and addresses, then since there are only two distinct names in the data, there will only be two records: one for "Smith" in "Huntsville" and one for "Jones" in "Huntsville". So ... which age (out of the three in the data) do you want returned for "Smith" and which age (17 or 18) do you want returned for "Jones"? Without knowing the answer to that it's impossible to even begin writing a query, because at the moment the requirement "(nevertheless age is retrieved)" is making no sense.

Try answering ahundiak's question: "What exactly would you like this distinct query to return?" Write out the rows you'd want the query to return from the example data.

Masna

I apologize for all the confusion. My situation is tough to explain (as you can see). I would like to return the first of the other values (fields), so in this case, age 17. I need to be able to retrieve all fields from a given table, but only the rows that follow along with the DISTINCT fields. Understand? (Again, I'm sorry, and I really appreciate your help.)

ahundiak

Ok. One thing is that there is no real concept of row order in a table. So you can't query for the first record of a set. You can grab all the rows, order by name and city then filter the result set in PHP to only process the first row for a given combination of name and city.

It's still not clear to me why your admin user would only want the first record. Are you just trying to get the column names?

Masna

No one understands me huh... I want to pull out rows based on a select few fields' DISTINCT values, but still get information for the other fields of those DISTINCT rows.