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:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT LastName, FirstName, Gender "
                   L"FROM Persons "
                   L"ORDER BY LastName;",
                   conADO,
                   ADODB::CursorTypeEnum::adOpenDynamic,
                   ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

In the same way, you can get the list of females followed by the list of males by ordering the list in alphabetical order based on the Gender. As an example, execute the following statement:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT LastName, FirstName, Gender "
                   L"FROM Persons "
                   L"ORDER BY Gender;",
                   conADO,
                   ADODB::CursorTypeEnum::adOpenDynamic,
                   ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

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:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT LastName, FirstName, Gender "
                   L"FROM Persons "
                   L"ORDER BY LastName ASC;",
                   conADO,
                   ADODB::CursorTypeEnum::adOpenDynamic,
                   ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

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:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L""SELECT LastName, FirstName, Gender "
                   L"FROM Persons "
                   L"ORDER BY LastName DESC;",
                   conADO,
                   ADODB::CursorTypeEnum::adOpenDynamic,
                   ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}