MX Query Builder Forum :: Distinct query

Hi I'm having real trouble assembling a query using query builder. I have attached an image showing my database structure, query and results. As you can see from my results I am getting 8 rows returned. What I need is 2 rows 1 row for each idMedVilla_Property in the table where the sort column is the least.

So out of those results I would like to just get the 1st and 6th rows.

I have tried all combinations of min,max,distinct etc.. but I'm getting nowhere.

I hope you can help it will be much apreciated.

Thanks in advance

- Andy

Hello,

To do this, you need to add a GROUP BY clause to the SQL like this:

SELECT ... GROUP BY idMedVilla_Property

Regards,
Razvan RACASANU

Hi,

Thanks, that almost works. I now only get two rows returned however the rows that are returned are not the ones with the lowest sort order. If the sort order of my images changes so the first row for example has a sort order of 2 and the second row has a sort order of 1 the first row as it appears in the DB is still returned not the row with the lowest sort order. How would I combine this GROUP BY query to only return the row with the lowest sort order?

If I set an ORDER BY img_sort it orders the results after the rows have been returned not before the join which is what I'm looking for.

Thanks again

- Andy

Hello,

To do this, you need to have unique values in the field img_sort, and you need to use a sub query, so your database must support this. The SQL should be:

SELECT * FROM MedVilla_Images WHERE img_sort IN (SELECT MIN(img_sort)
FROM MedVilla_Images GROUP BY idMedVilla_Property)

Regards,
Razvan RACASANU