I’m trying to get a specific set of results from a MySQL database. It’s easier to explain if I first show an simplified example database.

STRUCTURE--------------------------------
object: ID (primary key, Unique), ObjName, Number, GroupCode (foreign key)

objectgroup: ID, GroupName, GroupCode, CreationDate

DATA-------------------------------------
object:
1, "Apple", 1, "ABC"
2, "Apple", 1, "DEF"
3, "Bannana", 2, "ABC"
4, "Bannana", 5, "DEF"
5, "Carrot", 452, "ABC"
6, "Carrot", 73, "DEF"
7, "Carrot", 7, "DEF"
8, "Durian", 12, "ABC"
9, "Durian", 12, "GHI"
10, "Elderberry", 1, "DEF"
11, "Elderberry", 1, "JKL"

objectgroup:
"Group One", "ABC", 12-31-1999
"Group Two", "DEF", 1-1-2000
"Group Three", "GHI", 1-1-2000
"Group Four", "JKL", 12-31-2000

The results I’m trying to get are akin to the following:
| ObjName | Number | GroupCode |
| ——– | ——– | ——– |
| Apple | 1 | DEF |
| Bannana | 5 | DEF |
| Carrot | 7 | DEF |
| Durian | 12 | GHI |
| Elderberry | 1 | JKL |

Essentially, I want to eliminate duplicate results and return whichever is newer and has the lowest number.

Now, while this sounds easy to do (as I myself thought), it’s deceptively hard to pull off. Some flavors of SQL allow you to have individual columns in the SELECT statement be Unique(), but MySQL doesn’t allow that. It also forces you to include everything between SELECT and FROM in the GROUP BY statement (unless you turn off a specific variable, but the documentation notes that this would cause data to become unpredictable), so there’s no funny business you can do with that either. Despite all this, I’ve managed to get bits and pieces of the whole, so I will detail those now.

Restricting object Name’s by CreationDate is as simple as

SELECT ObjName, MAX(CreationDate) AS cD FROM object
LEFT JOIN objectgroup 
ON object.GroupCode = objectgroup.GroupCode
GROUP BY ObjName

which returns

However, this doesn’t have Number or GroupCode. As you can see in the example above, there are some group’s which have the same date, and there are some objects that are in a group twice but with different numbers, which makes the results above incredibly ambiguous.

I’ve also managed to figure out getting the lowest Number for each Name in a group:

SELECT o1.ObjName, o1.GroupCode, o1.Number FROM object AS o1
LEFT JOIN object AS o2
ON o1.ObjName = o2.ObjName
AND o1.GroupCode = o2.GroupCode
AND o1.Number > o2.Number
WHERE o2.ID IS NULL

which turns

6, "Carrot", 73, "DEF"
7, "Carrot", 7, "DEF"

into "Carrot", "DEF", 7

This works by joining the data of objects from o2 onto each of the corresponding objects that have a higher number. The rows where o2 has an ID are then left out of the final results, leaving behind only the ones that did not have a number smaller than them. WHERE o2.ID IS NULL only works with LEFT JOIN object AS o2 and not LEFT JOIN (SELECT * FROM object) AS o2 or similar.

I’ve attempted joining the Date restriction and the Number restriction together, but the problems noted earlier with the Date restriction rear their heads here. I can’t just JOIN the Number restriction onto the Date restriction because that gives me

I’d need an identifying field in the Date restriction to stop this from happening, but it’s not possible to do that and still do GROUP BY ObjName to remove the duplicate names.

Now, while I could stack JOINs until the cows come home, that is a very messy implementation and also hasn’t worked for me so far; either simply not doing anything (or in some cases, increasing the number of rows returned) or throwing the dreaded Error Code 1060: Duplicate column name, which happens when trying to join multiple SELECT * FROMs together. However, some of the failure with JOIN stacks may be due to my inexperience with MySQL, and I may simply be attempting it in the wrong way.