GREATEST / LEAST Function Alternatives in SQL Server

One of the most upvoted requests on feedback.azure.com is for Microsoft to add MAX/MIN as non-aggregate functions in SQL Server. In other words, the GREATEST and LEAST functions that we have been able to find in MySQL, PostgreSQL, and Oracle for a while.

These functions are fairly simple in nature. They’ll return the highest or lowest value from a list of values or fields. In other words, the greatest or least value out of values on the same row. So it’s a bit of a mystery why something so simple and readily available in other RDBMSes hasn’t been included in SQL Server…until now…kinda.

As of 4/14/21, Microsoft has officially announced that the GREATEST and LEAST functions are in Azure SQL Database and SQL Managed Instance. Unofficially, it seems they had silently included these functions in at least (no pun intended) SQL Managed Instance several months prior. In any case, here we are today with official Microsoft documentation on GREATEST and LEAST. This is all great news. What’s also great is that, in their statement, Microsoft stated they would be including these two functions in the next version of SQL Server.

But what about all of us on SQL Server 2019 and prior? Fortunately, there’s a way to mimic these two functions in your queries using a correlated subquery in the SELECT clause.

In the simplest form with just a list of static values, you can use this syntax:

SELECT (SELECT MAX(val) FROM (VALUES (10), (30), (20), (5)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (10), (30), (20), (5)) val_tbl(val)) AS Least;

Here’s an example querying a table of values:

SELECT t.val1,
       t.val2,
       t.val3,
       (SELECT MAX(val) FROM (VALUES (t.val1), (t.val2), (t.val3)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (t.val1), (t.val2), (t.val3)) val_tbl(val)) AS Least
FROM   (VALUES (151,275,179),
               (64,268,240),
               (216,29,156),
               (252,271,200)) t(val1, val2, val3);

Or, if you happen to have the Stack Overflow Database, you can try this example:

SELECT p.Id,
       p.AnswerCount,
       p.CommentCount,
       (SELECT MAX(val) FROM (VALUES (p.AnswerCount), (p.CommentCount)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (p.AnswerCount), (p.CommentCount)) val_tbl(val)) AS Least
FROM   Posts p;

While it would’ve been great to have had these functions all along, I’m happy they’re on their way and that there’s a viable alternative in the meantime.

T-SQL Tuesday #136: Blog About Your Favorite Data Type (Or Least Favorite)

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Brent Ozar (b|t) and the topic is “Blog About Your Favorite Data Type (Or Least Favorite)”

A Little Background

For those of you who don’t know, T-SQL Tuesday is the brainchild of SQL Server Guru Adam Machancic (t). It is a monthly blog party where a person is chosen to be the blog “host” for the month, chooses a topic (can be technical or non-technical), and charges IT professionals to write about the topic on their respective blogs, then link back to the host blog.

Once the month has concluded, the host makes a roundup post comprised of links to each participant’s post. It’s a great way for people to learn a lot about a topic from incredibly knowledgeable people, and it’s wonderful for the participants as they get to contribute to the community and the greater good.

I’m sorry but you’re not my type.

I was planning to kick this T-SQL Tuesday off by writing about my favorite SQL Server data type–the VARCHAR. Yes, it has many good qualities, not the least of which is its fairly high data type precedence, allowing it to be implicitly converted to several lower-precedence data types. A real forgiving type…

But instead I’ve decided to focus on a data type that I really, really dislike–TEXT. Sure, it sounds like it would fit the bill for…well…anywhere you need to store large amounts of text….but did you know that this fun data type has been deprecated since SQL Server 2005? That’s 16 years ago. 7 SQL Server versions ago. Yet it still finds its way into modern versions of SQL Server. I mean, sure, Microsoft has announced that it will be removed in a future version (in favor of VARCHAR(MAX)), but until then, here are some fun facts about the TEXT data type:

  1. You can’t use it with the = operator. You read that correctly. The most common operator in the WHERE clause. Can’t use it. You have to use LIKE or convert the field beforehand.
  2. You can’t use it in a UNION or UNION ALL because it’s not comparable.
  3. INTERSECT? Nope.
  4. EXCEPT? No.
  5. Well surely you can sort by it, right? Haha no.

See a pattern here?

It’s not me. It’s you.

It’s easy to see the myriad limitations of the TEXT data type–which is probably why Microsoft has encouraged developers to use VARCHAR(MAX) instead. It has served its purpose for earlier versions of SQL Server, but it’s time to move on.

And honestly, I don’t like focusing on the negative as there’s enough of that going around in this world, but I can make an exception for a data type that has no place in our beloved SQL Server databases.

T-SQL Tuesday #123: Life Hacks to Make Your Day Easier

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Jess Pomfret (b|t) and the topic is “Life hacks to make your day easier.”

A Little Background

For those of you who don’t know, T-SQL Tuesday is the brainchild of SQL Server Guru Adam Machancic (t). It is a monthly blog party where a person is chosen to be the blog “host” for the month, chooses a topic (can be technical or non-technical), and charges IT professionals to write about the topic on their respective blogs, then link back to the host blog.

Once the month has concluded, the host makes a roundup post comprised of links to each participant’s post. It’s a great way for people to learn a lot about a topic from incredibly knowledgeable people, and it’s wonderful for the participants as they get to contribute to the community and the greater good.

This 123rd T-SQL Tuesday is particularly special to me because it’s the first one I’ve participated in.

Life Hack

A life hack I learned about relatively recently is the Apple “Speak Screen” option on iPhones. This feature, which has apparently been around since the iOS 6 days, allows iPhones to speak the contents of web pages to you. How did I not know this existed?

When I first used this feature, I was blown away. My backlog of websites, blogs, and articles to read had been growing, and it seemed I would never make my way through all of the material. Now, when I’m in the shower, in the car, heck–even when I’m cleaning the house I can bring up a page I’ve been wanting to read and simply have Siri dictate it to me.

Here’s how to turn it on:

Settings –>Accessibility–>Spoken Content–>Toggle “Speak Screen” On

Or, just go to a web page ask Siri to “Speak Screen.” If it’s not enabled, it’ll give you the option to go to the aforementioned settings area and enable it; if it is enabled, it’ll speak the page to you.

There are several other related options you can set in this area, like whether text gets highlighted as it’s spoken, the rate at which text is spoken, etc.

As we all know, time is one of the few resources we simply cannot make more of, so our next best option is to be as efficient as we can with the time that we have available to us. “Speak Screen” has allowed me to significantly improve my efficiency and overall productivity. I highly recommend exploring this to see if it can help you too. If you want to do more reading on it, head over to Apple.

PASS Summit 2016

It was amazing, to say the least.

And yes, I know I’m a few months late to the game to blog about last year’s PASS Summit, but I only recently got my blog up and running and have had a chance to compile myriad pictures I took during the conference. Whether you’re relatively new to SQL Server and are considering going or a veteran who has been multiple times, I thought I’d share my experience in hopes that you’ll glean something from it–maybe a new nugget of information.

Continue reading “PASS Summit 2016”

Useful Link Countdown: PASS Summit 2016

PASS Summit 2016 Countdown:

;WITH the #sqlpass Summit 2016 only one month away, I’m counting down the next 30 days with 30 links I’ve found to be incredibly useful over the years! @sqlpass @microsoft #sqlserver

Continue reading “Useful Link Countdown: PASS Summit 2016”