|=--------------------------------------------------=|
  _________________  .____     ___________________
 /   _____/\_____  \ |    |    \_   _____/\______ \
 \_____  \  /  / \  \|    |     |    __)_  |    |  \
 /        \/   \_/.  \    |___  |        \ |    `   \
/_______  /\_____\ \_/_______ \/_______  //_______  /
        \/        \__>       \/        \/         \/


|=--------------------------------------------------=|
. |03.05 - Querying the data II.                     .
|=--------------------------------------------------=|

  Next we will examine some more data querying. Let's
assume we have table table named users. Selecting all
columns and all rows from this table:

  select * from users;

  Gives us following result:

    user_key  user_name    user_password
 ----------  -----------  ---------------
          1  user1        pass
          2  user2        pass
          3  user3        pass
          4  user4        pass

  Previously  we  selected  certain both  columns  or
rows. Now we would like  to sort the results. This is
done by  adding ORDER BY  clause to the  query. After
this the query can look like:

  select * from users a order by user_key desc;

  That would give use following result:

  user_key  user_name    user_password
----------  -----------  ---------------
         4  user4        pass
         3  user3        pass
         2  user2        pass
         1  user1        pass

  You  can  see  the  rows are  sorted  according  to
numeric order by the user_key column.

  Let's limit the result to only TOP n rows. We would
like to select last 4 rows in terms of the descending
user_key order:

  select * 
  from users a 
  order by user_key 
  desc limit 2;

  Here we added the lmit

    user_key  user_name    user_password
  ----------  -----------  ---------------
           4  user4        pass
           3  user3        pass

  In  the previous  queries  we added  alias for  the
table "a". From  now we can reference  to the columns
of the  certain table using  this alias. Now  we will
try to select 2 middle  rows when sorted by user name
descending. For  this purpose we will  use analytical
function  row_number(). This  function calculate  and
apply internal sort in the  inner query. Then we will
give alias  to the  inner query  "b". And  finally we
will apply filter to select only rows with row number
between 2 and 3.

  select rn, user_key, user_name from (
    select
      row_number() over (order by user_name desc) rn,
      a.user_key,
      a.user_name
    from users a
  ) b
  where b.rn between 2 and 3;

  Will give us following result:

  rn    user_key  user_name
----  ----------  -----------
   2           3  user3
   3           2  user2

  In the next section we will examine grouping.


|=--------------------------------------------------=|
; v  | Thanks Linux and Vim | buy me a coffee   | o |;
|=--------------------------------------------------=|