3 replies to this topic

[piskie]

Could somebody please tell me how to use "SELECT DISTNCT reference,name,company" to return additional fields in the same rows for example an additional field called "dated".

The Select I can get to work fine and the returned results are exactly as expected, but I need to use other fields in the same row. If I include them at the end of the 3 Distinct Fields, understandably, all records are returned.

I need to use the DISTINCT selector but return additional fields not included in the Distinct selection.

[Randy]

Not sure what the end goal is, so it's a little tough to give an answer that would be the best for the situation.

That said, there are a couple of ways you can approach it. If you're using DISTINCT to remove duplicates you may want to try substituting DISTINCTROW instead. It is similar to DISTINCT, but you get more row information, as hinted at by the name.

Or if you're trying to order the results you may also want to look into grabbing a larger dataset first, then use GROUP BY at the end of the process. For instance, if you wanted to display all four fields but have them grouped by Company it would look something like

CODE

SELECT reference,name,company,dated WHERE company !=0 GROUP BY company;

If you can give us a few more details about what you're hoping to have happen somebody here can probably provide a better answer. There are some really good code jockies running around these forums.

[piskie]

Thanks for that Randy.
My Table contains a number of Rows for each purchase "Reference"
What I would like is to return ALL Rows that have the same Reference, but return complete Rows.
Thus: SELECT DISTINCT reference ..........................

I can't seem however to get All fields in the Row returned. When I try using more fields than:

SELECT DISTNCT reference,name,company

I get a single Row when I add additional fields because the Query is in fact Unique. I do however want those fields to be returned without being part of the SELECT DISTINCT query.

There are typically say 6 or 8 rows that have the same "reference,name,company" with other fields different, but I want them returned as part of the result from the DISTINCT query.

I know I am not doing a good job of explaining this, but I hope that clarifies enough to get me an answer.

[Randy]

So Reference is your distinct value? Like an invoice number or some such? And do are you able to feed this distinct reference identifier to the query?

I think you may be going after it the wrong way around. Distinct in MySQL is kind of the other way around, in that it returns a single distinct value for each field you feed it. Which looks to be the opposite of what you're going for.

A couple of ideas.

CODE

SELECT * FROM tablename WHERE reference = $reference;

You could add GROUP BY to the end of this too if you need to group individual rows.

Or if you needed Distinct you might be able to use something like

CODE

SELECT DISTINCT * FROM tablename WHERE reference = $reference;

Untested of course. Still trying to wrap my head around what you're trying to end up with.