Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google      Sign In   
  • Create Account

Subscribe to HRA Now!

 



Are you a Google Analytics enthusiast?

Share and download Custom Google Analytics Reports, dashboards and advanced segments--for FREE! 

 



 

 www.CustomReportSharing.com 

From the folks who brought you High Rankings!



Photo
- - - - -

Select Distinct


  • Please log in to reply
3 replies to this topic

#1 piskie

piskie

    HR 7

  • Active Members
  • PipPipPipPipPipPipPip
  • 1,098 posts
  • Location:Cornwall

Posted 09 April 2010 - 02:48 PM

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.

#2 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 12 April 2010 - 12:15 PM

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. wink1.gif

#3 piskie

piskie

    HR 7

  • Active Members
  • PipPipPipPipPipPipPip
  • 1,098 posts
  • Location:Cornwall

Posted 14 April 2010 - 07:29 PM

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:
QUOTE
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.

#4 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 15 April 2010 - 12:18 PM

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

SPAM FREE FORUM!
 
If you are just registering to spam,
don't bother. You will be wasting your
time as your spam will never see the
light of day!