Clever Geek Handbook

Oracle database: how to select all first, but return specific columns?

Background

I have an oracle database table with a lot of columns that I run some queries on.

I don't know exactly what data I'm looking for in my query, so I want to return all columns, but I don't want to hunt and peck at columns that I know make sense.

Question

Suppose a table (Table 1) with column A, column B, column C .... Column Z is

Is there a way to essentially say, "Select column C, column J, column F, column Q, and then the rest of the columns from table 1"?

Things i have tried

Saving with pseudo sql, Run:

Select Column C, Column J, Column F, Table 1. * from Table 1

Doesn't help because while I don't mind duplicates, oracle sees them as undefined columns and hence returns an error.

+3


source to share


2 answers


There is no nice and easy way to do this other than specifying each column.

But if you don't mind duplicates and you don't need the column names, you can use these columns:

Select 
  ColumnC as ColumnC1, 
  ColumnJ as ColumnJ1, 
  ColumnF as ColumnF1,
  t.* 
from 
  Table1 as t


Just for demonstration, I've also used Table1. You can omit the keyword as

, but I feel like it makes it more readable.

Note that while these additional columns are not complex for Oracle queries, they generate additional traffic. For testing, this solution is ok, but in production code I would only select the columns you need and only select them once. This is just a little extra work. After all, how many columns do you have? :)

+12


source


However, you can work around this problem by clicking the columns that you specifically select. for example

SELECT columnC new_columnC, columnJ new_columnJ, columnF new_columnF, t.*
  FROM table1 t


so that there are no columns with the same name.

+5


source






More articles:


All Articles