Imagine you’re organizing a gaming tournament, and you want to assign a different prize based on the rank players achieved.

Let’s say you have the following rankings:

Ranking Table

Using CHOOSE

CHOOSE function returns the value from a list of values based on the specified index.


SELECT CHOOSE(Rank, 'Gold Medal', 'Silver Medal', 'Bronze Medal', 
'Participation Prize', 'Participation Prize') AS Prize FROM Rankings;

Result:

Explanation:

  • The CHOOSE function takes the Rank as an index.
  • If Rank is 1, it returns ‘Gold Medal’.
  • If Rank is 2, it returns ‘Silver Medal’.
  • If Rank is 3, it returns ‘Bronze Medal’.
  • If Rank is 4 or 5, it returns ‘Participation Prize’.

Using IIF:

IIF function returns one of two values depending on whether the specified condition evaluates to true or false.

SELECT Rank, IIF(Rank = 1, ‘Gold Medal’, ‘No Prize’) AS Prize FROM Rankings;

Result:

Explanation:

  • The IIF function checks if the Rank is 1.
  • If true, it returns ‘Gold Medal’.
  • If false, it returns ‘No Prize’.

In simple terms:

CHOOSE function selects a value based on the index provided.

IIF function returns one value if a condition is true, otherwise returns another value.

These functions are handy when you need to make decisions or select values based on certain conditions in your SQL queries.