Thursday, April 28, 2011

Working with SQLite in AIR: Part 5

In this tutorial we're still learning SQL.

From the previous tutorial we know how to select data in our table, we can, for example, select all values from a column in our table. Now imagine that your table is a discography of a music band, which has columns such as songname, album, duration, etc. Say, we want to extract all albums from it.

Of course we will have to select the column to do that:

SELECT album FROM discography

But there is a problem. Most of the time there are more than 1 songs in an album, so we're going to get multiple duplicates of the same album name value. To exclude all the clones, we can use the "DISTINCT" command with the "SELECT" one:

SELECT DISTINCT album FROM discography

This way we will only return each album once.

It is also possible to sort the results of the "SELECT" command. We can sort them by using an "ORDER BY" command when selecting data.

For example, we can sort the people in our contact book by ages:

SELECT fname, lname, age FROM contacts ORDER BY age

Now, the SQLite engine first selects the values and then sorts the resulted rows (that consist of 3 columns - fname, lname and age) by age.

By default, the sorting order is from the smallest to biggest (the smallest on top). We can change it the other way by adding "DESC" to our command.

SELECT fname, lname, age FROM contacts ORDER BY age DESC

We can include more than one columns in the "ORDER BY" part of the command. For example:

SELECT fname, lname, age FROM contacts ORDER BY age, fname

This way, the results will be first sorted by age. Then, if we have, for instance, more than one people that are 25 years old, the engine sorts them by the next criteria - their first names.

This is similar to sorting arrays in AS3.

That's all for now. Thanks for reading!

Related:

Working with SQLite in AIR: Part 1
Working with SQLite in AIR: Part 2
Working with SQLite in AIR: Part 3
Working with SQLite in AIR: Part 4
Working with SQLite in AIR: Part 6
Working with SQLite in AIR: Part 7
Working with SQLite in AIR: Part 8
Working with SQLite in AIR: Part 9
Working with SQLite in AIR: Part 10
Working with SQLite in AIR: Part 11
Working with SQLite in AIR: Part 12

No comments:

Post a Comment