CODEWITHSUNDEEP
sqloracle-databaseoracle11g
SeanKilleen
SeanKilleen

Reputation: 8977

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

Background

I have an oracle database table with a lot of columns that I'm running 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 for columns I know are meaningful.

Question

Supposing a table (Table 1) with Column A, Column B, Column C....Column Z --

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've Tried

Keeping with pseudo-sql, Running:

Select Column C, Column J, Column F, Table1.* from Table1

Doesn't help, because even though I don't mind the duplicates, oracle sees them as ambiguously defined columns and thus returns an error.

Upvotes: 5

Views: 20442

Answers (2)

GolezTrol
GolezTrol

Reputation: 116140

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

But if you don't mind the duplicates and you don't care about the column names, you could alias those columns:

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

Just for demonstration, I aliased Table1 as well. You may omit the as keyword, but I feel it makes it a little more readable.

Do note that while these extra columns are not at all difficult for Oracle to query, they do generate extra traffic. For testing, this solution is fine, but in production code, I would opt to only select the columns you need, and only select them once. It's only a little extra work. After all, how many columns do you have? :)

Upvotes: 14

Justin Cave
Justin Cave

Reputation: 231751

You can work around the problem, however, by aliasing the columns that you are specifically selecting. For example

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

to ensure that there are no identically named columns.

Upvotes: 6

Related Questions