aspose file tools*
The moose likes JDBC and the fly likes SELECT command is ordering int column values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SELECT command is ordering int column values" Watch "SELECT command is ordering int column values" New topic
Author

SELECT command is ordering int column values

Joel Christophel
Ranch Hand

Joined: Apr 20, 2011
Posts: 237
    
    1

I have implemented the following method to return the values of a column. The goal is to have the location in the column (row) correspond with their location in the array (index). This works with String columns, but with numerical columns, the ResultSet seems to place them in ascending order, thus making their positioning in the returned String array not reflect the positioning of the values in the column. 'rs' is a ResultSet reference variable.

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18564
    
    8

I don't understand what you mean by the "positioning of the values in the column". If you mean to say that the data doesn't arrive in the order you expected it to arrive, that's because your SELECT statement is missing an ORDER BY clause. (At least I assume that your ResultSet comes from the SQL query in your code -- it isn't apparent from your code where it actually comes from.) Anyway, if you don't tell the database what order to return the data in, it's free to return it in any order it sees fit.
Joel Christophel
Ranch Hand

Joined: Apr 20, 2011
Posts: 237
    
    1

Paul Clapham wrote:I don't understand what you mean by the "positioning of the values in the column".

What I am intending to do is order the values by their column indices.

Paul Clapham wrote:If you mean to say that the data doesn't arrive in the order you expected it to arrive, that's because your SELECT statement is missing an ORDER BY clause.

I don't know how to go about writing an ORDER BY clause that orders values by their indices.

Paul Clapham wrote:(At least I assume that your ResultSet comes from the SQL query in your code -- it isn't apparent from your code where it actually comes from.)

Isn't that what rs = statement.executeQuery(sql); indicates?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18564
    
    8

I'm sorry, I don't know what "column indices" means.
Joel Christophel
Ranch Hand

Joined: Apr 20, 2011
Posts: 237
    
    1

Paul Clapham wrote:I'm sorry, I don't know what "column indices" means.

There are two plural forms of the noun index: indices and indexes. Index 0 refers to the first value in the column, then index 1, then index 2, etc.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18564
    
    8

Joel Christophel wrote:There are two plural forms of the noun index: indices and indexes.

Yes, this part I understood.
Index 0 refers to the first value in the column, then index 1, then index 2, etc.

But this part implies a knowledge of what "first" means. I don't know anything about your data so I don't know what it means -- can you explain?
Joel Christophel
Ranch Hand

Joined: Apr 20, 2011
Posts: 237
    
    1

Paul Clapham wrote:But this part implies a knowledge of what "first" means. I don't know anything about your data so I don't know what it means -- can you explain?

Below is a picture of the table in question.


The column to which I am referring is the 3rd column, 'SchoolID'. I want to order my ResultSet from the first value in the column 'SchoolID' (101059) to the last value in the column 'SchoolID' (101051). Although the numbers currently happen to be in descending order from top to bottom, I don't want their numerical value to be taken into any consideration at all when I make my query. Index 0 of the returned Java array should contain the topmost value of the column, and index 8 of the returned Java array should contain the bottom-most value of the column. Did that clear things up?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1699
    
  14

Joel Christophel wrote:
The column to which I am referring is the 3rd column, 'SchoolID'. I want to order my ResultSet from the first value in the column 'SchoolID' (101059) to the last value in the column 'SchoolID' (101051). Although the numbers currently happen to be in descending order from top to bottom, I don't want their numerical value to be taken into any consideration at all when I make my query. Index 0 of the returned Java array should contain the topmost value of the column, and index 8 of the returned Java array should contain the bottom-most value of the column. Did that clear things up?

You do realise that there is no intrinsic order to your data records within the database table, don't you? Depending on when your records get inserted, how the table is partitioned, when the DBA decides to move the table spaces around, etc etc, your records will be physically stored in what is essentially an arbitrary order in the database. This is simply how relational databases work. The only reliable way to get the data back in a specific order is to specify the required order in your SQL ORDER BY clause.

I don't understand what order you want your data to be returned in, because the only visible order in your example is the descending order of the SchoolId, and you have explicitly stated that is not what you want. But unless you apply an ORDER BY clause to the table data (or to a value derived from that data) during the query, I don't see how you can expect to get it back in your desired order at all. If you have not stored the information you need to sort the data when you query it back, then maybe you need to add a column to your table and populate it with whatever secret ingredient your sorting process will require.

Perhaps you could explain what features of the three visible data values in each row determine which one comes top of your list, second in your list, and so on. When you've done that, maybe somebody can help you with your ORDER BY clause.


No more Blub for me, thank you, Vicar.
Joel Christophel
Ranch Hand

Joined: Apr 20, 2011
Posts: 237
    
    1

chris webster wrote:
You do realise that there is no intrinsic order to your data records within the database table, don't you? Depending on when your records get inserted, how the table is partitioned, when the DBA decides to move the table spaces around, etc etc, your records will be physically stored in what is essentially an arbitrary order in the database. This is simply how relational databases work. The only reliable way to get the data back in a specific order is to specify the required order in your SQL ORDER BY clause.

I don't understand what order you want your data to be returned in, because the only visible order in your example is the descending order of the SchoolId, and you have explicitly stated that is not what you want. But unless you apply an ORDER BY clause to the table data (or to a value derived from that data) during the query, I don't see how you can expect to get it back in your desired order at all. If you have not stored the information you need to sort the data when you query it back, then maybe you need to add a column to your table and populate it with whatever secret ingredient your sorting process will require.

Perhaps you could explain what features of the three visible data values in each row determine which one comes top of your list, second in your list, and so on. When you've done that, maybe somebody can help you with your ORDER BY clause.

When I apply the exact same block of code to the other columns in the table, it populates and returns String array that holds the values as they appear from top to bottom. For example, the array for the first column would look like the following: {"Joel", "Hannah", "Kevin", "Jeanette", "David", "Nathan", "Marquise", "Mayela", "Matthew"}. This is also, coincidentally the order in which they were chronologically entered into the table, but other than that, they do not appear to be ordered alphabetically or in any other way. My question is, why doesn't it treat numerical columns the same way, and how can I get it to?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1699
    
  14

Joel Christophel wrote:
When I apply the exact same block of code to the other columns in the table, it populates and returns String array that holds the values as they appear from top to bottom. For example, the array for the first column would look like the following: {"Joel", "Hannah", "Kevin", "Jeanette", "David", "Nathan", "Marquise", "Mayela", "Matthew"}. This is also, coincidentally the order in which they were chronologically entered into the table, but other than that, they do not appear to be ordered alphabetically or in any other way. My question is, why doesn't it treat numerical columns the same way, and how can I get it to?

  • Basically, your query will return the records in an arbitrary order unless you give it an explicit ORDER BY clause, regardless of which column(s) you are asking for.
  • It is not ordering the data differently for different columns, because it is not explicitly ordering the data at all. In any case, you should normally read all the columns you want for each record in one go, not via multiple passes through separate queries e.g.

  • Right now it happens to be returning the records in chronological order, but there is no guarantee it will continue to do so in future unless you tell it to do so.
  • If you want to get your records reliably in chronological order, you probably need to save them with an appropriate timestamp e.g. include and populate a column called DATE_CREATED when you create the records, then make sure you "ORDER BY date_created" in your query.
  • If you want to fetch the data in some other order, you need to define that order and use it in your ORDER BY clause.
  • Joel Christophel
    Ranch Hand

    Joined: Apr 20, 2011
    Posts: 237
        
        1

    chris webster wrote:
  • Basically, your query will return the records in an arbitrary order unless you give it an explicit ORDER BY clause, regardless of which column(s) you are asking for.
  • It is not ordering the data differently for different columns, because it is not explicitly ordering the data at all. In any case, you should normally read all the columns you want for each record in one go, not via multiple passes through separate queries e.g.

  • Right now it happens to be returning the records in chronological order, but there is no guarantee it will continue to do so in future unless you tell it to do so.
  • If you want to get your records reliably in chronological order, you probably need to save them with an appropriate timestamp e.g. include and populate a column called DATE_CREATED when you create the records, then make sure you "ORDER BY date_created" in your query.
  • If you want to fetch the data in some other order, you need to define that order and use it in your ORDER BY clause.

  • Thanks, that definitely cleared things up for me. However, that's not really what I wanted to hear; I don't get why tables don't have a built in row/index column. It seems like what I'm trying to do will work by either using a timestamp column or just creating a row number column. I'll try the latter.
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1699
        
      14

    Joel Christophel wrote:
    Thanks, that definitely cleared things up for me. However, that's not really what I wanted to hear; I don't get why tables don't have a built in row/index column. It seems like what I'm trying to do will work by either using a timestamp column or just creating a row number column. I'll try the latter.

    Relational databases are intended to support multiple ways of using the same data, usually in large volumes and often with new uses arising years after the data was originally modelled. Your preferred sorting order may not be mine (e.g. you want chronological, I want alphabetical), and the internal physical structure (indexing, partitioning etc) place other demands on how the data is "ordered". One sorting order cannot fit all potential uses, but happily we have a very easy solution: the SQL "ORDER BY" clause.

    If you are ordering by a number column, remember that it may not be the same as chronological ordering, and if you are using this numeric value as a primary key, you'll need to make sure it's unique and is always populated on INSERT and never updated. If all you're looking for is chronological sorting, I'd go for the timestamp column if I were you.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: SELECT command is ordering int column values