OraFAQ Forum: SQL & PL/SQL » how to display any particular column first using select * query.

Home » SQL & PL/SQL » SQL & PL/SQL » how to display any particular column first using select * query. (oracle 10g sql plus)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to display any particular column first using select * query. [message #618375]

Fri, 11 July 2014 06:49

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

Is there any way to display any particular column first using select * query. I do not want to mention column names in select statement as we do to change the display order of columns.
for example , i have have table with 50 columns & i want to display a 25th column from table in the begining .
is it possible or is there any other way to achieve this.

Thanks

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618377 is a reply to message #618375]

Fri, 11 July 2014 07:07

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Fri, 11 July 2014 17:19

Is there any way to display any particular column first using select * query.

NO.

Quote:

I do not want to mention column names in select statement as we do to change the display order of columns.

That's being too lazy and waiting for future issues...Writing "Select * from table" in a code is not a good idea.

Quote:

i have have table with 50 columns & i want to display a 25th column from table in the begining .

You need to mention the 25th column name at beginning. No shortcuts.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618380 is a reply to message #618377]

Fri, 11 July 2014 07:19

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

Quote:

That's being too lazy and waiting for future issues...Writing "Select * from table" in a code is not a good idea.

@lalit ok then what can one do if there are more than 100 columns , will he go to write each and every column , don't you think it will take a lot of time , i think there must be a solution , doesn't matter it should be using select * query or typing column names only.
there can be any other method for which i am searching for.

Thanks

[Updated on: Fri, 11 July 2014 07:20]

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618383 is a reply to message #618380]

Fri, 11 July 2014 07:36

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Come on, man. Using * is universally acknowledged to be bad practice. Of course, at your next code review you can explain "I always follow bad practices because I am lazy" and see what reaction you get.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618386 is a reply to message #618383]

Fri, 11 July 2014 07:41

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

Quote:

Come on, man. Using * is universally acknowledged to be bad practice. Of course, at your next code review you can explain "I always follow bad practices because I am lazy" and see what reaction you get.

n i said

Quote:

i think there must be a solution , doesn't matter it should be using select * query or typing column names only.
there can be any other method for which i am searching for.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618388 is a reply to message #618380]

Fri, 11 July 2014 07:44

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

jgjeetu wrote on Fri, 11 July 2014 07:19

@lalit ok then what can one do if there are more than 100 columns , will he go to write each and every column , don't you think it will take a lot of time , i think there must be a solution , doesn't matter it should be using select * query or typing column names only.
there can be any other method for which i am searching for.

Thanks

"A lot of time"?
It would take me about one minute, start to finish. Less if I were using a good editor like vi. Copy and paste are our friends, but only if we use them.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618389 is a reply to message #618386]

Fri, 11 July 2014 07:45

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

The only solution for you is to stop using bad practices and apend some time to learn and understand the good practices.
Shortcuts will lead to wasting more time in future.
It's completely up to you.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618392 is a reply to message #618389]

Fri, 11 July 2014 08:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>@lalit ok then what can one do if there are more than 100 columns , will
most likely flawed design & data is not actually Normalized.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618394 is a reply to message #618392]

Fri, 11 July 2014 08:27

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You know I'd KILL for the out the box behaviour in 12c and up to be invisible columns by default to stop this stuff.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618396 is a reply to message #618388]

Fri, 11 July 2014 08:52

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

EdStevens wrote on Fri, 11 July 2014 13:44

It would take me about one minute, start to finish. Less if I were using a good editor like vi. Copy and paste are our friends, but only if we use them.

I assume you mean "yank" rather than "copy" Smile

Smile

I think they should teach vi in the kindergarten.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618429 is a reply to message #618396]

Fri, 11 July 2014 11:38

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Back to the original question: if you don't mind having that "n"th column repeated, here's what I usually do if I, for example, want to display the JOB column first:

SQL> desc emp
 Name                                                 Null?    Type
 ---------------------------------------------------- -------- ------------------------------------
 EMPNO                                                NOT NULL NUMBER(4)
 ENAME                                                         VARCHAR2(10)
 JOB                                                           VARCHAR2(9)
 MGR                                                           NUMBER(4)
 HIREDATE                                                      DATE
 SAL                                                           NUMBER(7,2)
 COMM                                                          NUMBER(7,2)
 DEPTNO                                                        NUMBER(2)

SQL> select e.job, e.* from emp e;

JOB            EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
--------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
CLERK           7369 SMITH      CLERK           7902 17.12.1980       1600                    20
SALESMAN        7499 ALLEN      SALESMAN        7698 20.02.1981       2400        300         30
SALESMAN        7521 WARD       SALESMAN        7698 22.02.1981       2050        500         30
MANAGER         7566 JONES      MANAGER         7839 02.04.1981       3775                    20
SALESMAN        7654 MARTIN     SALESMAN        7698 28.09.1981       2050       1400         30
MANAGER         7698 BLAKE      MANAGER         7839 01.05.1981       3650                    30
MANAGER         7782 CLARK      MANAGER         7839 09.06.1981       3250                    10
ANALYST         7788 SCOTT      ANALYST         7566 09.12.1982       3800                    20
PRESIDENT       7839 KING       PRESIDENT            17.11.1981       5800                    10
SALESMAN        7844 TURNER     SALESMAN        7698 08.09.1981       2300                    30
CLERK           7876 ADAMS      CLERK           7788 12.01.1983       1900                    20
CLERK           7900 JAMES      CLERK           7698 03.12.1981       1750                    30
ANALYST         7902 FORD       ANALYST         7566 03.12.1981       3800                    20
CLERK           7934 MILLER     CLERK           7782 23.12.1982       2100                    10

14 rows selected.

SQL>

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618430 is a reply to message #618429]

Fri, 11 July 2014 11:44

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

LF, good idea for few columns. But OP has a peculiar, rather strange requirement that the entire column list display changes frequently.

jgjeetu wrote on Fri, 11 July 2014 17:19

we do to change the display order of columns.

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618455 is a reply to message #618430]

Sat, 12 July 2014 01:01

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

as an aside to SELECT *, if you are working on an EXADATA system, select * can be a killer because it negates the value of COLUMN PROJECTION during SMARTSCAN operations. COLUMN PROJECTION is possibly the most important performance feature of EXADATA SMARTSCAN.

Just another example of why select * is not a great idea.

Kevin

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618566 is a reply to message #618429]

Sun, 13 July 2014 10:38

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

@LF , that's what i was looking for thanks Smile

Smile

@lalit , i got the solution bro , i have no problem if the column get repeated again.

[Updated on: Sun, 13 July 2014 10:40]

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618568 is a reply to message #618566]

Sun, 13 July 2014 11:39

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Sun, 13 July 2014 21:08

@LF , that's what i was looking for thanks

But only for a very few columns, be aware that if the column list in the beginning are too many per your preferences, then you would end up duplicating too many columns. Up to you, YOYO!

jgjeetu wrote on Sun, 13 July 2014 21:08

@lalit , i got the solution bro , i have no problem if the column get repeated again.

Ok, let me know your view when you start working for an application. For example, if you want to send a dataset to frontend application, and you keep changing the column order in display, just imagine the amount of rework you keep doing in BE as well as FE. I think it's simply unnecessary. God knows how would you handle it while returning dataset to multiple screens to a frontend.

And another point, though not much of your concern. Most of the robust applications look for reusable components, and you would never ever be a part of it with this approach.

Regards,
Lalit

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618573 is a reply to message #618568]

Sun, 13 July 2014 13:22

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't look at that "solution" as something anyone would want to use for anything serious, except for quickly displaying table's contents.

I use TOAD; it is a GUI which nicely displays result of "SELECT * FROM", but sometimes table has too many columns and the one I'm particularly interested in is somewhere far right. Then I use that technique to move it to the beginning of the column list. Why don't I select only that (or two or three) columns? Because I'm also interested in contents of another columns.

SQL*Plus is certainly not a tool where "SELECT * FROM" looks pretty; it usually requires too many adjustments (column formatting, linesize, whatever). SQL*Plus simply forces you to carefully choose which columns to display. True, that's what we did back then when GUI wasn't available. Now (with TOAD), I don't care: "SELECT * FROM" works just fine most of the time (once again: for a quick view).

Report message to a moderator

Re: how to display any particular column first using select * query. [message #618575 is a reply to message #618573]

Sun, 13 July 2014 13:52

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

No problem using tools like TOAD or PLSQL Developer as far as we know the pros and cons. The question here is not about the tools specifically. Its about to OVERCOME THE LAZINESS TO CODE. Isn't it? Thats what OP kept saying in the entire thread.

Even I use PLSQL Developer for code development and I have my own configured .uew file to take care of the indentation and formatting. I know how to use it so I have no issues using it for specific purpose.

Coming back to the question, whenever I want to look at a specific column values, all I need to do is use the tool's feature to show it in ascending/descending order column wise, I just need to click on the button and rest is taken care by the tool. I know my column name starts with some specific character, so scroll down and look at the corresponding value. And thus my work is done. So far so good, tool helped me to do all the stuff which SQL*Plus would take ages to help me. But, OP's question is to display columns in his own required order which "select *" in any tool would fail to do so. A one time activity is still understood, but modifying the column order every time to display is something which needs manual effort and no tool can ever guarantee to take care of it.

Report message to a moderator

Previous Topic:	Order of WHERE columns doesn't matter? The optimizer will handle that?
Next Topic:	how to get script of table in sql plus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 06:06:02 CDT 2024