LIMIT, ORDER BY

 

LIMIT


The LIMIT statement is useful when you want to see just the first few rows of a table. This can be much faster for loading than if we load the entire dataset.


The LIMIT command is always the very last part of a query. An example of showing just the first 10 rows of the orders table with all of the columns might look like the following:


SELECT *
FROM orders
LIMIT 10;


We could also change the number of rows by changing the 10 to any other number of rows.


ORDER BY


The ORDER BY statement allows us to sort our results using the data in any column. If you are familiar with Excel or Google Sheets, using ORDER BY is similar to sorting a sheet using a column. A key difference, however, is that using ORDER BY in a SQL query only has temporary effects, for the results of that query, unlike sorting a sheet by column in Excel or Sheets.

The ORDER BY statement always comes in a query after the SELECT and FROM statements, but before the LIMIT statement. If you are using the LIMIT statement, it will always appear last. As you learn additional commands, the order of these statements will matter more.

Solutions to previous ORDER BY questions


  1. Write a query to return the 10 earliest orders in the orders table. Include the id, occurred_at, and total_amt_usd.

SELECT id, occurred_at, total_amt_usd
FROM orders
ORDER BY occurred_at
LIMIT 10;

  1. Write a query to return the top 5 orders in terms of largest total_amt_usd. Include the id, account_id, and total_amt_usd.

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC 
LIMIT 5;

  1. Write a query to return the lowest 20 orders in terms of smallest total_amt_usd. Include the id, account_id, and total_amt_usd.

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd
LIMIT 20;

When you provide a list of columns in an ORDER BY command, the sorting occurs using the leftmost column in your list first, then the next column from the left, and so on. We still have the ability to flip the way we order using DESC.

  1. Write a query that displays the order ID, account ID, and total dollar amount for all the orders, sorted first by the account ID (in ascending order), and then by the total dollar amount (in descending order).

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY account_id, total_amt_usd DESC;

  1. Now write a query that again displays order ID, account ID, and total dollar amount for each order, but this time sorted first by total dollar amount (in descending order), and then by account ID (in ascending order).

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC, account_id;

  1. Compare the results of these two queries above. How are the results different when you switch the column you sort on first?

In query #1, all of the orders for each account ID are grouped together, and then within each of those groupings, the orders appear from the greatest order amount to the least. In query #2, since you sorted by the total dollar amount first, the orders appear from greatest to least regardless of which account ID they were from. Then they are sorted by account ID next. (The secondary sorting by account ID is difficult to see here, since only if there were two orders with equal total dollar amounts would there need to be any sorting by account ID.)

Comments

Post a Comment

Popular posts from this blog

Entity Relationship Diagrams

Image
  Entity Relationship Diagrams From the last lesson, you might remember that an  entity relationship diagram  (ERD) is a common way to view data in a database. It is also a key element to understanding how we can pull data from multiple tables. It will be beneficial to have an idea of what the ERD looks like for Parch & Posey handy, so I have posted it again below.  You might even print a copy to have with you as you work through the exercises in the remaining content. Tables & Columns In the Parch & Posey database there are 5 tables: web_events accounts orders sales_reps region You will notice some of the columns in the tables have  PK  or  FK  next to the column name, while other columns don't have a label at all. If you look a little closer, you might notice that the  PK  is associated with the first column in every table. The  PK  here stands for  primary key .  A primary key exists in every table, and...
Read more

Arithmetic Operators

  Arithmetic Operators Derived Columns Creating a new column that is a combination of existing columns is known as a  derived  column (or "calculated" or "computed" column). Usually you want to give a name, or "alias," to your new column using the  AS  keyword. This derived column, and its alias, are generally only temporary, existing just for the duration of your query. The next time you run a query and access this table, the new column will not be there. If you are deriving the new column from existing columns using a mathematical expression, then these familiar mathematical operators will be useful: *  (Multiplication) +  (Addition) -  (Subtraction) /  (Division) Consider this example: SELECT id , ( standard_amt_usd / total_amt_usd ) * 100 AS std_percent , total_amt_usd FROM orders LIMIT 10 ; Here we divide the standard paper dollar amount by the total order amount to find the standard paper percent for the order, and use the  ...
Read more

NOT

  NOT The  NOT  operator is an extremely useful operator for working with the previous two operators we introduced:  IN  and  LIKE . By specifying  NOT LIKE  or  NOT IN , we can grab all of the rows that do not meet a particular criteria. Questions using the NOT operator We can pull all of the rows that were excluded from the queries in the previous two concepts with our new operator. Use the  accounts  table to find the account name, primary poc, and sales rep id for all stores except Walmart, Target, and Nordstrom. Use the  web_events  table to find all information regarding individuals who were contacted via any method except using  organic  or  adwords  methods. Use the  accounts  table to find: All the companies whose names do not start with 'C'. All companies whose names do not contain the string 'one' somewhere in the name. All companies whose names do not end with 's'. Solutions to NOT...
Read more