DISTINCT MySQL Query But Exclude A Column From DISTINCT Criteria

Ryan Bayne
Ryan Bayne used Ask the Experts™
on
Hey, worded the title as best I could.

I have a DISTINCT query, works great but I now need the records to include the record ID. In my table the column is named eciid. Adding eciid as the query is right now simply makes that column a part of the DISTINCT criteria and returns even more records.

I only need one of the ID's from a group of records sharing the same DISTINCT values from one or two columns (that part does not matter). I just thought I would ask for advice on the best approach to do this.

My query is below and I changed the second line to this...

DISTINCT eciid,'.$columns.'

Then after testing I realise why it cannot be done this way.

Considered querying the table for each returned record from this query to get a single ID for a single record that shares the distinct values. Either way I look at this, there is a lot of queries to be performed in a loop of say 10 DISTINCT records.

Thanks in advance for a recommended approach

$records = $wpdb->get_results( 'SELECT
	DISTINCT '.$columns.' 
	FROM '. $csv['sql']['tablename'] .' 
	WHERE '.$where.'
	LIMIT '.$number.'',ARRAY_A );
	return $records;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try GROUP BY '.$columns.' , this will only give u the distinct $columns and eciid associated with it

$records = $wpdb->get_results( 'SELECT
      eciid, '.$columns.'
      FROM '. $csv['sql']['tablename'] .'
      WHERE '.$where.'
        GROUP BY '.$columns.'
      LIMIT '.$number.'',ARRAY_A );
      return $records;
Ryan BayneWordPress Developer

Author

Commented:
Great that worked a treat.

I'm glad I asked, I usually spend 2 hours Google searching and trying various things. I don't think I would have considered GROUP BY because I had no idea it worked like that.

Thanks for a quick reply and keeping me moving on this project
I am glad it solved your problem.  From your query I can see that you have good control over programming. I would suggest you to go through  some of the MySQL features, if you are planning to use it extensively in your project.
Ryan BayneWordPress Developer

Author

Commented:
Done a couple of SQL exams at Uni. But most of my work is the basics, occasional LEFT JOIN.

But that is good advice. Maybe I need to do a refresher and make a point of refreshing on any web technology. To not have something ready in mind, well you know yourself it can change the entire approach you take to writing a script.

Hard way or easy way!

Thanks again

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial