CODEWITHSUNDEEP
sqlsql-server
tiddi rastogi
tiddi rastogi

Reputation: 526

Using * in SELECT query can we place the columns in a different order?

I have written a sql query as

select 
    a.beam_current, 
    * 
from 
    INDUS2_MPS.dbo.main_mps_hs_analog b 
        inner join INDUS2_BDS.dbo.DCCT a on a.logtime=b.logtime 
where 
    a.logtime BETWEEN '2014-10-10 07:17:00' AND '2014-10-10 08:47:00'

When I execute this query, I get all required columns. Now I want to change the ordering of columns. First column of INDUS2_MPS.dbo.main_mps_hs_analog i.e. logtime I want to be my first column in output and a.beam_current to be second column and then rest of the columns of INDUS2_MPS.dbo.main_mps_hs_analog should be followed.

In order to achieve this,how to manipulate my above query???

Upvotes: 0

Views: 94

Answers (3)

wonderbell
wonderbell

Reputation: 1126

You can give different aliases to the first two columns in select query, and then select all columns from query

select 
    b.logtime [fistCol],
    a.beam_current [secondCol], 
    b.* 
from 
    INDUS2_MPS.dbo.main_mps_hs_analog b 
        inner join INDUS2_BDS.dbo.DCCT a on a.logtime=b.logtime 
where 
    a.logtime BETWEEN '2014-10-10 07:17:00' AND '2014-10-10 08:47:00'

Upvotes: 0

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

select 
    b.col1, a.col1, b.col2, b.col3, a.col10, b.col5+a.col69 -- list any columns in any order here
from 
    INDUS2_MPS.dbo.main_mps_hs_analog b 
        inner join INDUS2_BDS.dbo.DCCT a on a.logtime=b.logtime 
where 
    a.logtime BETWEEN '2014-10-10 07:17:00' AND '2014-10-10 08:47:00'

Upvotes: 1

Jim Horn
Jim Horn

Reputation: 889

The above answers are correct, just add the columns in whatever order you want. btw, using * in a query with more than one JOINed object means that all columns from all objects are returned (a and b).

First column of INDUS2_MPS.dbo.main_mps_hs_analog i.e. logtime and then rest of the columns of INDUS2_MPS.dbo.main_mps_hs_analog should be followed.

You probably want to spell out every column, otherwise if you do a SELECT b.logtime, a.beam_current, b.* FROM ... it will return the logtime column twice. If that's acceptable and you don't want to type 200 column names then go for it.

Upvotes: 1

Related Questions