SQL ORDER BY

5 Key excerpts on "SQL ORDER BY"

• 

  eBook - ePub

  Querying MariaDB

  Use SQL Operations, Data Extraction, and Custom Queries to Make your MariaDB Database Analytics more Accessible (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  Sometimes you will be faced with a table where some fields contain the same data elements repeated several times. A telephone directory is like this. You may have many pages of people named Smith, although nearly all may have different first names. Even if the last name and the first name are the same, they may have different middle initials. In these cases, you need to sort the data on successive fields so that (to continue the telephone directory analogy) you sort first by last name, then by first name, and finally by middle initial.

  Entering several fields after the ORDER BY keyword tells SQL to sort the data progressively on the fields that you entered. In this example, it means the following:

  • First by the country
  • Then by the make (if there is more than one record with the same country)
  • Finally by the model (if there is more than one record with the same country and make)

  All you have to do is enter the field names separated by a comma in the ORDER BY clause.

  Tricks and Traps

  Applying a multiple sort order has its own specific set of core requirements. These include the following:

  • While there may be technical limits to the number of fields you can sort on, in practice you do not need to worry and can extend the field list that you use in the ORDER BY statement to include many fields.
  • In this example, you used the same fields in the SELECT statement that you used in the ORDER BY statement. This is not compulsory in SQL because there is no obligation in a basic SQL query to display the same fields that you use for ordering the data. However, when you are testing your SQL skills (or ensuring that the data looks like you think it should), it can be a good idea to use the same groups of fields in both clauses. This way you can see whether the output is what you expect.
  • Sorting query results requires a lot of computing horsepower when you are dealing with large tables or small database servers. I recommend that you sort data only if it is really necessary.
  • As was the case for SELECT clauses, you should not add a comma after the final field name in the ORDER BY clause.

  16. Limiting the Number of Records Displayed

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introductory Relational Database Design for Business, with Microsoft Access

  • Jonathan Eckstein, Bonnie R. Schultz(Authors)
  • 2017(Publication Date)
  • Wiley

    (Publisher)

  You can use ORDER BY in queries either with or without aggregation. As an example of an ORDER BY query without aggregation, consider:

  SELECT FirstName, LastName, OrderDate FROM CUSTOMER, ORDERS WHERE CUSTOMER.CustomerID = ORDERS.CustomerID ORDER BY OrderDate DESC, LastName, FirstName;

  This query displays the customer first name, customer last name, and date of each order, sorted from the most recent orders to the oldest. Orders placed on the same date are presented alphabetically by customer last name; orders placed on the same date by customers with the same last name are presented alphabetically by customer first name (although this consideration does not affect this particular small database). This query produces the output shown in Table 10.12 .

  Table 10.12

  Orders sorted by customer name and date.

  First Name	Last Name	Order Date
  Geoffrey	Hammer	5/1/2013
  Geoffrey	Hammer	5/1/2013
  Joseph	Brower	4/30/2013
  Derek	Escher	4/29/2013
  Derek	Escher	4/29/2013
  Robert	Sloan	4/28/2013
  Robert	Sloan	4/27/2013
  Geoffrey	Hammer	4/26/2013
  Laura	Ng	4/26/2013
  Laura	Ng	4/26/2013
  Geoffrey	Hammer	4/25/2013
  Xiaoming	Wang	4/25/2013
  Margerita	Colon	4/24/2013
  Ashley	Flannery	4/24/2013
  Benjamin	Masterson	4/24/2013
  Benjamin	Masterson	4/22/2013
  Mary	Milgrom	4/22/2013
  Mary	Milgrom	4/22/2013
  Benjamin	Masterson	4/21/2013
  Mary	Milgrom	4/21/2013
  Benjamin	Masterson	4/20/2013
  Ashley	Flannery	4/18/2013
  Leonard	Goodman	4/18/2013
  Geoffrey	Hammer	4/18/2013
  Margerita	Colon	4/15/2013

  The Overall Conceptual Structure of Queries

  In summary, SQL queries have the following general form:

  SELECT {DISTINCT} expressions1“{}” means DISTINCT is

  optional

  FROM data_source

  WHERE conditions1 Optional

  GROUP BY expressions2 Optional

  HAVING conditions2 Optional

  ORDER BY expressions3 Optional

  ;

  Conceptually, the sequence of operations is as follows:

  1. We form the Cartesian join of the tables appearing in the FROM clause. This table consists of all possible combinations of records from the constituent tables of the query. If we use the INNER JOIN syntax instead of separating tables with commas, those specific inner joins are made.7

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn SQL Database Programming

  Query and manipulate databases from popular relational database servers using SQL

  • Josephine Bush(Author)
  • 2020(Publication Date)
  • Packt Publishing

    (Publisher)

  Do not depend on the order of the rows in a result set, unless you have specified an ORDER BY clause. The order in which rows are returned may or may not be the same without an ORDER BY explicitly defined in your query.

  To sort the columns in ascending order, use the ASC keyword, and to order them in descending order, use the DESC keyword. To sort a table by g_all in the appearances table, you can execute the following query:

  USE lahmansbaseballdb;SELECT playerid, g_all, g_batting, g_defense FROM appearancesORDER BY g_all; The previous query will give you the results shown in the following screenshot:

  The previous query doesn't have a WHERE clause, but if it required one, then you should place it between the FROM and ORDER BY clauses, as shown in the following query:

  USE lahmansbaseballdb;SELECT playerid, g_all, g_batting, g_defense FROM appearancesWHERE playerid LIKE 'a%'ORDER BY g_all; To sort in descending order instead, you can add the DESC keyword to your ORDER BY clause, as shown in the following query: USE lahmansbaseballdb;SELECT playerid, g_all, g_batting, g_defense FROM appearancesORDER BY g_all DESC; The previous query will give you the results shown in the following screenshot. You can see that g_all has the highest game total at the top of the results now: You can also ORDER BY columns that aren't specified in your SELECT clause. You need to specify them in the ORDER BY clause by the exact column name in the table. Next, we will learn how to sort by one or more columns. Passage contains an image

  Learning how to use the ORDER BY clause to sort by one or more columns

  Let's say you wanted to sort on more than one column. To do this, you should place the columns you want to order by in the ORDER BY clause in the order in which you want them ordered. For instance, if you wanted to order by playerid, then g_all, you can execute

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • Auerbach Publications

    (Publisher)

  Sorting with the ORDER BY clause allows resorting to an order other than the natural physical order in which rows were originally added to a table. This first example sorts by AUTHOR_ID contained within the name of the author (the NAME column), somewhat counter intuitively thought from the inside out and known in SQL as a NAME by AUTHOR_ID sort (names sorted by authors):

  SELECT * FROM AUTHOR ORDER BY NAME, AUTHOR_ID; AUTHOR_ID NAME ---------- -------------------    8 Gavin Powell    3 Isaac Azimov    2 James Blish    5 Jerry Pournelle    7 Kurt Vonnegut    4 Larry Niven    1 Orson Scott Card    6 William Shakespeare

  It is not recommended to omit the ORDER BY clause if any kind of sorted order is required but some queries will be sorted naturally without use of the ORDER BY clause, depending on data retrieved, whether tables or indexes are read and which clauses are used. Different databases have different formats for ORDER BY clause syntax, and some formats are more restrictive than others.

  4.2.3    Aggregating with the GROUP BY Clause

  An aggregated query uses the GROUP BY clause to summarize repeating groups of rows into aggregations or summaries of those groups. The syntax below adds the syntax for the GROUP BY clause:

  SELECT… FROM table [alias] [,… ] [ WHERE. ] [ GROUP BY expression [, … ]  [ HAVING condition ]  ] [ ORDER BY… ];

  The GROUP BY clause is optional to the SELECT command and the HAVING clause is an optional clause of the GROUP BY clause.

  Note the sequence of the different clauses in the syntax diagram above, in that the WHERE clause is always executed first and the ORDER BY clause is always executed last. It follows that the GROUP BY clause will always appear between the WHERE clause and the ORDER BY clause.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Data Engineering with dbt

  A practical guide to building a cloud-based, pragmatic, and dependable data platform with SQL

  • Roberto Zagni(Author)
  • 2023(Publication Date)
  • Packt Publishing

    (Publisher)

  to 20. Important note

  If no ORDER BY clause is present, the order of the rows is undefined and could differ in each execution of the same query. In this case, the result of a LIMIT clause is non-deterministic because what rows are returned depends on the order in which the rows happen in the result set.

  Query clause order of evaluation

  In the previous sections, we have seen all the clauses that can appear in a SELECT statement.

  Now is a good time to bring your attention to the fact that these clauses are generally evaluated in the following specific order, as well as what it is important to pay attention to for each clause:

  1. FROM and its JOIN subclause, which are used to identify the source data for the query.
  2. The WHERE clause, which is used to filter out the source data that we do not want.

  This is probably the most important clause for performance, because the less data a query works on, the quicker it is. Use WHERE whenever possible to just bring in the data you need.

  1. The GROUP BY clause, which groups the source data left after applying the WHERE clause and calculates the aggregate functions on the grouped data.
  2. The HAVING clause, which filters on the results of GROUP BY .
  3. Partitioning of the windows and calculation of the window functions .
  4. The QUALIFY clause, which filters on the results of the window functions.
  5. The DISTINCT keyword, if applied to the SELECT clause, which removes duplicated rows.
  6. The ORDER BY clause, which puts the resulting rows in the desired order.
  7. The LIMIT clause, which caps the rows returned by the query to the desired amount.

  SQL operators

  When writing queries, we can perform operations on the data handled by the query.

  We do so by building expressions that return the desired value, using functions and operators.

  We can perform an operation pretty much everywhere a value is expected: in the SELECT clause to provide the desired outputs by transforming the inputs, in the WHERE

  Sign up to read

  Learn more about book