List Arrangement

Sorting Records

The lists of records we got above with the SELECT statement were presented in the same order they were created in the table. SQL allows you to arrange records in alphabetical order, in chronological order or in numeric incremental order. 

After SELECTing a series of columns, you may want to list the records following an alphabetical order from one specific field. To get an alphabetical or an incremental order of records, you must let the database know what field would be used as reference. In SQL, this is done using the ORDER BY expression. The syntax used would be:

SELECT What FROM WhatObject ORDER BY WhatField;

The column used as the basis must be recognized as part of the selected columns. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT LastName, FirstName, Gender " & _

                       "FROM Persons " & _

                       "ORDER BY LastName;", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

Rearranging a list of records is referred to as sorting:

• If the list is made or numeric values, the lowest value would become the first, followed by the second to lowest value, and the highest value would become the last. This is the case for the values in a COUNTER or an AUTOINCREMENT column of a table
• If the list is made of strings (words), the alphabet would be used. The string whose first letter is the highest in the alphabet from a, b, c, etc would be the first. In this case, the ascending order is the same as the alphabetical order. For example, in a list made of Paul, Walter, Anne, and John, in that order, when sorted in ascending order, the list would become Anne, John, Paul, Walter. If various strings in the list start with the same letter, the strings with the same starting letter would be grouped first. Then among the strings with the same starting letter, the second letter would be considered and the same algorithm would be applied
• If the list is made of dates, the earliest date would become the first and the most recent date would become the last
• If the list is a combination of numbers and strings, the numbers would be arranged in incremental order first, followed by the list of strings in alphabetical order
• If the list contains empty values, the empty values would be the first, the other values would be arranged in order depending on their type

Sorting in Ascending Order

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order including the first and last names, execute the following statement:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT LastName, FirstName, Gender " & _

                       "FROM Persons " & _

                       "ORDER BY LastName ASC;", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

Sorting in Descending Order

If you want to sort records in reverse order, you can use the DESC keywords instead. It produces the opposite result to the ASC effect. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT LastName, FirstName, Gender " & _

                       "FROM Persons " & _

                       "ORDER BY LastName DESC;", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

 

Conditional Selection

 

Introduction

Instead of using all data as we have done so far using the SELECT keyword, you can present a condition that the database would follow to isolate specific records. One of the keywords you can use to formulate conditions is WHERE. Its basic syntax is:

SELECT What FROM WhatObject WHERE Expression;

The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor is called a criterion. Although a group of expressions, making it plural is called criteria, the word criteria is sometimes used for a singular expression also. 

The expression is written using the formula:

ColumnName=Value

The ColumnName factor must be an existing column of the table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Persons " & _

                       "WHERE Gender='Male';", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

If it is a number, you can type its numeric value. 

The TRUE and FALSE Constants

In Boolean algebra, something is considered TRUE when it holds a value. The value is also considered as 1 or Yes. By contrast, if something doesn't hold a value, it is considered non-existent and non-worthy of consideration. Such a thing has a value of FALSE, 0, or No. To retrieve such a value, you can just find out if the value of a field exists or not.

The comparison for a True or False value can be performed on a Boolean column. If a record has a value of 1, the table considers that such a field is True. If the field has a 0 value, then it holds a FALSE value.

The NULL Constant

While the True and False constants are used to find out whether a Boolean expression has a positive or a negative value, the database provides another constant used to find out whether a field is empty. This can be done using the NULL constant.

When a field holds a value, the value would be considered using the comparison operators we will learn shortly. If the field is empty, it holds a value of NULL.

 

Filtering Records With Boolean Operators

 

Equality =

The equality operator is used to compare two values for similarity. The syntax of this operation is:

Value1 = Value2

If Value1 and Value2 hold the same value, the comparison produces a TRUE result. If they hold different values, the comparison renders a FALSE value. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Persons " & _

                       "WHERE Gender='Male';", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

 

Inequality <>

To find out if two fields hold different values, you can use the inequality operator which is represented by <>. Its syntax is:

Value1 <> Value2

This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a TRUE value. If they hold the same value, the comparison produces FALSE. This shows that the equality (=) and the inequality (<>) operators are opposite each other.

Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Persons " & _

                       "WHERE Gender<>'Female';", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

 

Less Than <

The "Less Than" operator uses the following syntax:

Value1 < Value2

If Value1 holds a value that is lower than that of Value2, the comparison produces TRUE. If Value1 holds a value that is greater than or similar to that of Value2, the comparison renders FALSE.

Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\Students.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Students " & _

                       "WHERE DOB < '1988-06-01';", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

 

Less Than or Equal <=

When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with the "Less Than Or Equal To" operator. It is represented by <= and its formula is:

Value1 <= Value2

If both operands (Value1 and Value2) hold the same value, then the comparison produces a TRUE result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a TRUE result. By contrast, if the value of Value1 is higher than that of Value2, the comparison renders a FALSE result. Notice that the > and the <= operators are opposite each other.

Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\Students.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Students " & _

                       "WHERE DOB =< '1988-06-01';", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

Greater Than >

The > operator is used to find out whether one value is "Greater Than" another. Its syntax is:

Value1 > Value2

The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces TRUE. Otherwise, the comparison produces FALSE. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces FALSE.

Greater Than or Equal >=

If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is:

Value1 >= Value2

If both Value1 and Value2 hold the same value, then the comparison renders a TRUE result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces TRUE. If the value of Value1 is less than the value of Value2, the comparison produces a FALSE result. Therefore, < and >= are opposite.

The NOT Operator

To deny the presence, the availability, or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that FALSE is the opposite of TRUE. In the same way, TRUE is the opposite of FALSE. If you want to compare a value as not being TRUE, the NOT TRUE would produce the same result as the FALSE value. For the same reason, the expression NOT FALSE is the same as TRUE. 

Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Persons " & _

                       "WHERE NOT (Gender = 'Male');", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub 

The IS Operator

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Persons " & _

                       "WHERE Gender IS NULL;", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

In the same way, to validate that something is not null, you can use the expression IS NOT NULL. 

 Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.Recordset = New ADODB.Recordset

        Dim conADO As ADODB.Connection = New ADODB.Connection



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Persons " & _

                       "WHERE Gender IS NOT NULL;", _

                       conADO, _

                       ADODB.CursorTypeEnum.adOpenDynamic, _

                       ADODB.LockTypeEnum.adLockOptimistic, 0)



        rstPeople.Close()

End Sub

 

Comparison Operators: IN

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator.