Clever Geek Handbook

Oracle DB simple SELECT where column order

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

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

CHOOSE a.chapter, a. other columns FROM A

Also, I can't just type:

CHOOSE a.chapter, a. *

because he will select "chapter" twice

I know the SQL query seems simple, but if I know how to solve this problem, I can extrapolate that thought to more complex areas. Also suppose I can't just scroll to find the "chapter" column and drag it to the beginning.

thank

0


source to share


6 answers


You don't have to select * in the program. As your schema evolves, this will lead to things you don't already know. Think about what happens when someone adds a column with the entire book? A request that you think would be very cheap suddenly starts bringing in megabytes of data.



This means that you have to list every column you need.

+6


source


Your best bet is to just select each column explicitly.



+6


source


A quick 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 no chapterCol column is already specified there.;))

+4


source


If you are going to embed "SELECT *" in your code, I would highly recommend it. As previous authors have pointed out, your code setup is aborted if a column is ever added (or removed) from the table. A simple tip is not to.

If you use this in development tools (view data, etc.). Then I would recommend creating a view with the desired column order. Grab the output from "SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS" and create a select statement for the view with the desired column order.

+4


source


This is how I would structure your query without entering all the names, but with some manual effort.

Start with "Select a.chapter"

Now do another fetch on your database like this:

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

now take the result out of that, in cut and paste mode, and add it to where you started. Now run this query. This should be what you asked for.

TA-dah!

+2


source


Unless you have a reason to do so, you shouldn't use SELECT * in queries. It aborts your application every time the schema changes.

0


source






More articles:


All Articles