Oracle DB simple SELECT where column order matters

Question

I am doing a simple SELECT statement in an Oracle DB and need to select the columns in a somewhat-specific order. Example:

Table A has 100 attributes, one of which is "chapter" that occurs somewhere in the order of columns in the table. I need to select the data with "chapter" first and the remaining columns after in no particular order. Essentially, my statement needs to read something like:

SELECT a.chapter, a. the remaining columns FROM A

Furthermore, I cannot simply type:

SELECT a.chapter, a.*

because this will select "chapter" twice

I know the SQL statement seems simple, but if I know how to solve this problem, I can extrapolate this thought into more complicated areas. Also, let's assume that I can't just scroll over to find the "chapter" column and drag it to the beginning.

Thanks

Answer 1

A quickie way to get around this would be SELECT a.chapter AS chapterCol, a.* FROM table a; This means there will be one column name chapterCol (assuming there's not a column already there named chapterCol. ;))

Answer 2

You should not select * in a program. As your schema evolves it will bring in things you do not know yet. Think about what happens when someone add a column with the whole book in it? The query you thought would be very cheap suddenly starts to bring in megabytes of data.

That means you have to specify every column you need.

Answer 3

Unless you have a very good reason to do so, you should not use SELECT * in queries. It will break your application every time the schema changes.

Answer 4

This is how I would build your query without having to type all the names in, but with some manual effort.

Start with "Select a.chapter"

Now perform another select on your data base as follows :

select ','|| column_name from user_tab_cols where table_name = your_real_table_name and column_name <> 'CHAPTER';

now take the output from that, in a cut-and-paste manner and append it to what you started with. Now run that query. It should be what you asked for.

Ta-da!

Answer 5

Your best bet is just to select each column explicitly.

Answer 6

If your going to embed the 'SELECT *' into program code, then I would strongly recommend against doing that. As noted by the previous authors, your setting up the code to break if a column is ever added to (or removed from) the table. The simple advice is don't do it.

If your using this in development tools (viewing the data, and the like). Then, I'd recommend creating a view with the specific column order you need. Capture the output from 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS' and create a select statement for the view with the column order you need.