Avatar of Ryan Bayne
Ryan Bayne
Flag for United Kingdom of Great Britain and Northern Ireland asked on

DISTINCT MySQL Query But Exclude A Column From DISTINCT Criteria

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

MySQL ServerPHP

Avatar of undefined
Last Comment
Ryan Bayne

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PranjalShah

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ryan Bayne

ASKER
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
PranjalShah

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 Bayne

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23