_________________ .____ __________________

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

|=--------------------------------------------------=|
. |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.

|=--------------------------------------------------=|
|=--------------------------------------------------=|