The Catch Block #17 - SQL Stupidity, Extreme Stress, Fiddler, and A Tale of Two Developers

Welcome to the 17th edition of The Catch Block!

Let's jump right in!

A Stupid SQL Ordering Dilemma

I have some data that looks like this:

One of the requests from our customers this last week was to see each ID/Name combo, ordered by the most-recent-first timestamps. So, what I want to end up with is this:

Surprisingly, this is not as easy as it sounds.

Attempt #1

Here's the first attempt I made at a SQL statement to do this:

SELECT DISTINCT ID, Name FROM tablename
ORDER BY Timestamp descending

Obviously that's not going to work, because you end up with the exact same result set, just missing the timestamp.

Attempt #2

My next thought was, "well, maybe we should make a subquery for the IDs and Names, and then query the distinct values out of that." Which results in this query:

SELECT DISTINCT ID, Name FROM 
    (SELECT ID, Name, Timestamp FROM tablename
     ORDER BY ch.EventTimestamp DESC) A

This looks like it should give the correct response: select the ID and Name, order them by their Timestamps, and then select the distinct ID/Name pairs. But what it actually gives is this:

Which is not what I want! It ordered the names and IDs in numeric order. But why?

Attempt #3

My next thought was to force an order by adding EventTimestamp to the SELECT clause and then ordering by it:

SELECT DISTINCT ID, Nam, Timestamp FROM 
    (SELECT ID, Name, Timestamp FROM tablename
     ORDER BY ch.EventTimestamp DESC) A
ORDER BY Timestamp DESC

But this isn't going to work either, because now the DISTINCT clause is operating on the Timestamp, so we just get the original dataset again.

It was at this point that I decided that maybe I should stop and research why Attempt #2 had failed, and I came across this helpful StackOverflow post:

"This all has to do with the "logical ordering" of SQL statements. Although a DBMS might actually retrieve the data according to all sorts of clever strategies, it has to behave according to some predictable logic. As such, the different parts of an SQL query can be considered to be processed "before" or "after" one another in terms of how that logic behaves.

"As it happens, the ORDER BY clause is the very last step in that logical sequence, so it can't change the behaviour of "earlier" steps.

This post didn't directly give me the answer I was seeking, but it set me on the right path.

Final Attempt

What I needed to do now was force SQL to operate on the query in the order I expected, and it turns out you can do this with an application of the GROUP BY clause and the MAX() function. Even better, I didn't need to use DISTINCT at all:

SELECT ID, Name, MAX(timestamp) FROM cc_claim cl 
GROUP BY ID, Name
ORDER BY MAX(timestamp) DESC

This query gives the following result set:

Which is, finally, exactly what the customers wanted.

Phew! Who knew SQL could be so difficult? But now, at least, you Dear Readers will know when you encounter this problem, how to solve it.

Previews and Announcements

• Introducing Fiddler Jam - I don't normally put non-Microsoft announcements here, but Fiddler is amazing and this particular release will be very helpful, particularly for non-technical users.

Quality Reads

• Sustaining Performance Under Extreme Stress (Troy Hunt)
• How to Build a Roman Numeral Converter and an Interactive Roman Numerals Chart (Alexander Arobelidze)
• A Few Different Ways to Concatenate Strings in C# (Vladimir Pecanac) - I always forget about String.Concat().
• Showing a loading screen while initializing a Blazor application (Gérald Barré)
• A tale of two developers (Dror Helper)
• Custom validation logic on client AND server with Blazor? (Jon Hilton)
• Always Use the for…of loop to iterate in JavaScript (Dhananjay Kumar)
• Building and consuming GraphQL API in ASP.NET Core 3.1 (Joydip Kanjilal)
• Always bet on HTML – being misunderstood (Christian Heilmann)

From the Archives

Be Like Us Or Else - Desperation, Failure, and New Job Regret

I’ve only held three full-time software development jobs since graduating fromcollege, including my current one. The second one was supposed to be my bigbreakthrough, my time to shine and become part of a well-oiled team of kickassprogrammers. That was not what happened. What actually happened …

Exception Not FoundMatthew Jones

Catch Up with the Previous Issue!

The Catch Block #16 - Not Normal, Lizard Brains, Scrum, and Blazor

Plus resilience, Conway, Markdown, a misuse of C#, and why scrum is maybe not such a good idea after all.

Exception Not FoundMatthew Jones

Thanks for reading, and we'll see you next week! Happy Coding!