Let’s Use Spreadsheet Query Function (7) Sorting basics “ORDER BY” clause

クエリイメージ Spreadsheet
この記事は約5分で読めます。
Advertisements

Target Readers

  • who are interested in SQL, but who are troublesome to prepare the environment and data from scratch
  • who are extracting data with vlookup, but who feel the limit
  • who want to analyze data
  • Even non-programmers who want to use queries
  • who are asking queries delicious? (meaning that the query is delicious)

Query function series planned (planned)

  1. Outline and purpose
  2. Syntax of query function
  3. Basic SELECT clause (get all, get only specific columns)
  4. Extraction basics Try using WHERE clause (numerical value match, size, date match, size etc.)
  5. Try using the GROUP BY clause
  6. Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. Sorting basics ORDER BY clause ← this time
  8. Required if you do SQL Try a subquery
  9. There are line breaks in the data. contains/starts with helps when you can’t handle like

Query function:Basics of “Order By” clause

Now Order by is a basic sort function. The basic query notation is as follows.

=query(data range,"select * order by E desc")

I will explain based on the above example.

Yellow underlined part:Select target column to sort by “order by” + ”column”

Red Underline part: desc(descending order)、asc(ascending order)

Notes on sorting by asc

Let’s say you have the following data sorted in ascending order by a function like this:

=query(A1:H8,"select * order by F asc")

Interestingly, there are blanks in the data.

The reason is that blank data is included in the original data range, so the blank part appears at the top as the minimum value. This is when the data range is specified as a whole column. . .

Maybe the data doesn’t appear in the display range at all. So, I will explain how to deal with it.

Use “is not null” to exclude blanks (missing values)

Now, how to deal with it. Use NULL, which was explained in the WHERE clause before. See the following for past articles.

Actually, the blank can be deleted neatly as follows.

=query(A1:H8,"select * where F is not null order by F asc")

Summary

It was an explanation of ORDER BY. To be honest, I don’t use it much personally because it only sorts, so honestly there is not much news.

It is a query element that can be used to some extent simple, so it may be good to use for practice.

Well, the next is a subquery, but I personally use this, so please look forward to it.

Series: Let’s Use Spreadsheet Query Function

While not studying SQL, I will become accustomed to using the spreadsheet query function. We will study from the simple usage of select statement, including the difference from SQL.

  1. Let’s Use Spreadsheet Query Function (1) Outline and Purpose
  2. Let’s Use Spreadsheet Query Function (2) Structure of query function
  3. Let’s Use Spreadsheet Query Function (3) SELECT Clause
  4. Let’s Use Spreadsheet Query Function (4) WHERE Clause
  5. Let’s Use Spreadsheet Query Function (5) Group by
  6. Let’s Use Spreadsheet Query Function (6) Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. Let’s Use Spreadsheet Query Function (7) Sorting basics “ORDER BY” clause
  8. Let’s Use Spreadsheet Query Function (8)Required if you do SQL!! Try a subquery with the Query function
  9. Let’s Use Spreadsheet Query Function (9)line breaks in the data etc. Query function error countermeasures

コメント