Using ORDER BY to sort data

This is the data that I would like to sort.

AAAAAAAA    0.0.0.0   hs01.stuff.net
BBBBBBBB    0.0.0.0   hs01.morestuff.net
CCCCCCCC    0.0.0.0   hs01.evenmorestuff.net
DDDDDDDD    0.0.0.0   hs01.stuff.net
EEEEEEEE    0.0.0.0   hs01.stuff.net
FFFFFFFF    0.0.0.0   hs01.evenmorestuff.net
GGGGGGGG    0.0.0.0   hs01.stuff.net
HHHHHHHH    0.0.0.0   hs01.evenmorestuff.net

This is the result of the ORDER BY:

AAAAAAAA    0.0.0.0   hs01.stuff.net
BBBBBBBB    0.0.0.0   hs01.morestuff.net
CCCCCCCC    0.0.0.0   hs01.evenmorestuff.net
DDDDDDDD    0.0.0.0   hs01.stuff.net
EEEEEEEE    0.0.0.0   hs01.stuff.net
FFFFFFFF    0.0.0.0   hs01.evenmorestuff.net
GGGGGGGG    0.0.0.0   hs01.stuff.net
HHHHHHHH    0.0.0.0   hs01.evenmorestuff.net

THE SAME OUTPUT, so something went wrong.

This is what I have tried thus far

$dbh_source2 = DBI->
connect("dbi:Oracle:host=????;port=????;sid=????",'????','????');

$SEL = "SELECT DISTINCT 
               PE_LOOPBACK_IP,
               PE_FQDN 
          FROM TABLE_NAME
         WHERE SITE_NAME = ? 
      ORDER BY PE_FQDN";

$sth = $dbh_source2->prepare($SEL);

This does not work and does not sort the last column by the name. Does ORDER BY only sort based on the first character? Are the numbers that are within the name causing it to fai开发者_开发知识库l?

Here is the code that I use to display the data:

print '<table border=1>';
print '<tr>';
print '<th>Tower name</th>';
print '<th>SUR IP</th>';
print '<th>SUR FQDN</th>';
print '</tr>';
foreach my $data_line (@raw_data) {
        chomp $data_line;

        $sth->execute($data_line);

        while (my @row = $sth->fetchrow_array ) {
              #Print data into cells#
              print "<tr>";
              print "<td>$data_line</td>";
              foreach (@row) {
                print "<td>$_</td>";
              }
              print "</tr>";
              #print "<$data_line>\t @row\n";

    }
}
print "</table>";

---

It isn't the SQL !

The perl code shows two loops.

The outer loop is traversed for each entry in the @raw_data array. The value of each @raw_data 'record' is passed as the SITE_NAME parameter to a SELECT statement.

You are executing multiple SELECT statements, one for each different site. Each individual SELECT returns an ordered row set, but they are all returning a single row for a single site. It is the order of the site names in the @raw_data array that is driving the order of the output.

<----------------------------------------------------------------------> Old entry which was totally misleading.

I'll offer a few possibilities.

(1) It is a bug. DISTINCT often, but not necessarily, does a sort to remove duplicates. It MAY have, for some reason, determined that the ORDER BY is redundant because of the query plan chosen.

To determine this, check the query plan. EXPLAIN PLAN is simplest, but it is better to use DBMS_XPLAN.DISPLAY_CURSOR with the sql_id from v$sql for that SQL.

(2) The data is being sorted but there is something causing the sort order to be other than what you expect. Try replacing PE_FQDN with DUMP(PE_FQDN) PE_FQDN. This will show the bytes that it is actually using.

(3) the SELECT doesn't look exactly how you have said. For example

SELECT FRED BILL, BILL BILL_TOO
FROM table
ORDER BY BILL

will actually order by the column that has the alias BILL (which is the column FRED) and not by the column BILL.

SELECT FRED, BULL
FROM table
ORDER BY 'FRED'

will sort by the string 'FRED', not the value in the column FRED (and since everything is sorted by the same value, it won't actually do anything).

<-------------------------------------------------------------------------------------->

---

Looks like your SQL is doing exactly what you're asking it to. You execute your statement eight times. Each time the result set contains only one row, so the the sort does nothing.

You have two options. You can either change your SQL so that you do one select which returns all of the rows that you want sorted how you want it. Or you keep your existing SQL but store the data returned from each execution in an array and sort that array before displaying the results.