RIRedinPA Posted May 14, 2009 Share Posted May 14, 2009 I'm sure this has been addressed before but I searched through the forums and I didn't find the answer I was looking for. I have a db table with 20 fields, one of which is company name. There are about 600 records, on average 5 of those will be the same company name. I need to filter the results so I have only one company name record for each company but I also need the other 19 fields from that record. I tried this - "SELECT DISTINCT companyname, colname1, colname2, colname3, colname4, colname5...ORDER BY companyname ASC The results returned every record. I should have only gotten 105 or so back. How can I build a query that will provide the return I need? Thanks Quote Link to comment Share on other sites More sharing options...
kickstart Posted May 14, 2009 Share Posted May 14, 2009 Hi Are the other 19 columns that you are bringing back constant for a company name? If not how do you want MySQL to determine which rows values to bring back? All the best Keith Quote Link to comment Share on other sites More sharing options...
RIRedinPA Posted May 14, 2009 Author Share Posted May 14, 2009 Hi Are the other 19 columns that you are bringing back constant for a company name? If not how do you want MySQL to determine which rows values to bring back? All the best Keith Thanks for the reply. The other columns are contact info for someone at that company, company url and some internal admin data, so if I have records like this: 1 company x user x title department address city state zip phone email url 2 company x user x2 title department address city state zip phone email url 3 company x user x3 title department address city state zip phone email url 4 company y user y title department address city state zip phone email url 5 company y user y2 title department address city state zip phone email url I'd like the results to just have just the first instance of company x and company y (2 results instead of 5) I'm assuming I am getting everything because mysql is seeing each row as distinct (different name, email or something) Quote Link to comment Share on other sites More sharing options...
kickstart Posted May 14, 2009 Share Posted May 14, 2009 Hi Yep. It tries to eliminate the duplicate rows, but there aren't any. You want something like this (not tested) SELECT a.companyname, colname1, colname2, colname3, colname4, colname5 FROM (SELECT companyname, max(colname1) AS Latestcolname1 FROM CompanyTable GROUP BY companyname) a JOIN CompanyTable b ON a.companyname = b.companyname AND a.Latestcolname1 = b.colname1 ORDER BY companyname ASC Basically do a select on the table and get the single relevant record for each company (I have just assumed the highest value of colname1) and do a JOIN with the full table to get all the other data for that row. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.