In our continuing adventures with the TOP operator in SQL, we’ve seen it do a lot of things that probably aren’t obvious when you first think about using TOP – especially if you’ve been using SQL for a while. Let’s say that our wonderful Product team asks us to get the top 1 % of…… Continue reading There Has to be Room at the TOP!
Tag: T-SQL
What to do with TOP when there are ties
In the last few blog posts – see here and here – we’ve been discussing the usage of the TOP operator. The product team will often ask for the top 5 of something or even the top 10% of performers in a subregion. There’s an additional part to the TOP operator that you won’t see…… Continue reading What to do with TOP when there are ties
Getting the TOP Performers
In the last blog post, we went over the extreme basics of using the TOP operator in SQL. We showed how to grab things like the TOP 10 of a certain item. That ability will get you through a number of criteria that you will be asked to perform. But what if you’re asked to…… Continue reading Getting the TOP Performers
TOP of the Mornin’ to You!
Sometimes you find yourself stuck in a rut and you never even know that there is a rut. That’s where I found myself today my friends! For years – and I do mean years – when I’ve been asked for the first 10 rows from a query, I would do the following: SELECT TOP 10…… Continue reading TOP of the Mornin’ to You!
More Fun with Smushing Data Together
Last week, we learned all about the old-style string concatenation and the new-ish, hip CONCAT function. This week, it’s time to talk about the CONCATs lesser talked about, but equally helpful cousin – CONCAT_WS. With CONCAT, we formatted the person’s name in a LastName, FirstName format using the following query. SELECT CONCAT(TRIM(LastName), ‘, ‘, TRIM(FirstName))…… Continue reading More Fun with Smushing Data Together
Sometimes You Need to Push Strings Together
Every now and again, you’re probably going to have to slap a few strings together in your fun with SQL. A long time ago (20 years ago), in SQL Server world, there was only one way to do it – and sadly, you’ll probably see many developers still doing it this way. SELECT LastName +…… Continue reading Sometimes You Need to Push Strings Together
Something to Remember with APPLY
I bet y’all thought that we had completely exhausted the topic of how to use APPLY – whether it be CROSS APPLY or OUTER APPLY. Well, guess what? Nope. Just nope. The word to remember when using either of the APPLY operators is deterministic. A deterministic function, as defined by O’Reilly, “always returns the same…… Continue reading Something to Remember with APPLY
CROSS vs OUTER: Knockdown drag-out free for all
In this corner, with a total of 10 letters – we have CROSS APPLY. In the other corner, also with a total of 10 letters – we have OUTER APPLY. Yes, that’s right. From the outside, there’s truly not a lot of difference between the two apply operators. Both of them work very well with…… Continue reading CROSS vs OUTER: Knockdown drag-out free for all
STRING_SPLIT is to be used with what?
My friends! Last time together, we discussed using the STRING_SPLIT function and how it’s used in combination with the CROSS APPLY. First off, most of us are used to working with an INNER JOIN instead of CROSS APPLY. Well, you’re not going to be able to use an INNER JOIN when you’re using the STRING_SPLIT…… Continue reading STRING_SPLIT is to be used with what?
Have Separated Fields? Make Them Rows!
Sometimes, you’ll have a lot of comma-separated data in your databases. Maybe you’ll have lots of data shoved into one field separated only by a vertical bar. As we all should be asking, “How do I get that data out of that one field and separated into several rows?” Well, I’m glad you asked, my…… Continue reading Have Separated Fields? Make Them Rows!