I usually talk about MySQL in hateful terms, so I’ll make an exception for that today.

Let’s say you want do find a distinct field in a group of data, except most of the other fields are non distinct. For example, say you are looking at a product shipping table and just want to know the information for the last product that shipped in a month.

Product | Address | Price | Shipment_ID | Customer_ID
1 | 123 Main St | $2 | 1020201 | 1001
2 | 312 Main St | $2 | 1020202 | 1002
2 | 666 Main St | $1 | 1020203 | 1003
1 | 881 Main St | $2 | 1020204 | 1004
1 | 42 Main St | $3 | 1020205 | 1005

In this data set, what you want is just two records, one for product 1 and the other for product 2. Except you don’t want just those two records, you want the full row for each of those items.

To do this in SQL requires a bit of a annoying workaround. If you tried to do something simple like:

SELECT * FROM Product_Table GROUP BY Product

That would obviously not work becase you need to aggregate each of the fields you are going to include, which is annoying for non-numberic fields, not to mention that it makes the rows non-unique. So you have to do funky things with HAVING.  Except, I just found out by accident that the command given above Just Works in MySQL, and gives the behaviour I actually wanted.  You just need to order it the way you want, and you can get distinct rows in complete violation of proper SQL syntax.  Nifty!

Of course, my supervisor suggested that I not use this anyhow, as undocumented hack “features” might go away in the future… and he’s right.  But still, cool.