Extreme Values

We wish to get rows of a table where a particular column takes its largest or smallest values.

Note that the n largest or smallest values might not necessarily correspond to n rows. If there are rows that take the same values, n values would correspond to more than n rows.

Largest Values

We wish to get the rows with the largest values for a particular column. In this example, we wish to get the rows where col_1 has its 5 highest values.

SELECT *
FROM refcon.dataset.table_1
ORDER BY col_1 DESC
LIMIT 5;

Here is how this works:

  • We sort the table in descending order using ORDER BY clause.
  • LIMIT clause will only return 5 rows.

Smallest Values

We wish to get the rows with the smallest values for a particular column. In this example, we wish to get the rows where col_1 has its 5 smallest values.

SELECT *
FROM refcon.dataset.table_1
ORDER BY col_1
LIMIT 5;

Here is how this works:

This code works similarly to the code above except that we order in ascending order (default behavior) instead of descending.

SQL
I/O