Click here to Skip to main content
15,569,234 members
Please Sign up or sign in to vote.
1.44/5 (2 votes)
See more:
how can I select one or more columns from a table by column-index and NOT by columnname?
I got the columnIndices of table by using this query

SELECT      name, colid
FROM        sys.syscolumns
WHERE      (id =
                    (SELECT      id
                    FROM        sys.sysobjects
                    WHERE      (name = 'table_name')))

OP has posted his resolution as an answer.
Updated 20-Jan-11 19:34pm
Sandeep Mewara 21-Jan-11 0:33am    
It's better to elaborate why you need to do something like this! That would really help in answering it.

Columns have no specific order, so retrieving them by index would be a) pointless, and b) unpredictable.
Share this answer
Sergey Alexandrovich Kryukov 20-Jan-11 12:54pm    
A 5. What is was? Idea of relational abuse?
Brady Kelly 23-Feb-15 11:37am    
Columns have exactly the order they are defined in for the table or query they are part of. Just about nothing but an ALTER TABLE can alter the order of columns in a standard relational table. The relational model only has the feature that rows are never guaranteed to be returned in any order if none is specified. The world is full of SQL with columns referred to by index.
The column-index is for internal use for the database. Of course you can query the indexes, but they don't mean much. While in most part they column index will be the order the columns created, but they can change if you delete or insert columns.

There is no need or usage in using the column-index in your query, unless you are certain it will NEVER change AND you are willing to take consequence and add headache whenever it changes.

Make your like easier by using column names.
Share this answer
Sergey Alexandrovich Kryukov 20-Jan-11 12:56pm    
Makes sense - my 5. The OP's idea was relational abuse, I would say.
I've had to do something like this in the past when working with a couple of products/technologies that didn't play together. I had to use columns that were called A B and C, though, but the solution might be similar.

You can create a view and rename the column names in the process mapping from the name to a number.

So, you'd do something like:
select firstname as [1], lastname as [2] from mytable;

If you do that, you'd be able to select by a numbered column system, but I'm not sure if this helps your particular situation.

Share this answer
I got my answer.

Here is the query by which we can select records by column index

Declare @WhichOne int;
Declare @Sql varchar(200);
Set @WhichOne = 2;
With cte As
(Select name, Row_Number() Over (Order By column_id) As rn
From sys.columns
Where Object_Name(object_id) = 'MyTable')
Select @Sql = 'Select ' + QuoteName(name) + ' From MyTable'
From cte
Where rn = @WhichOne;
Share this answer
Sandeep Mewara 28-Jun-12 4:32am    
Comment from Ren Vilo:
Hi Markand_bhatt,

I have a query that run's in a while statement. The query brings back all the columns in the tables while xxxxx

I only want to bring back the first 5 columns. The query above works but it only brings back the 1st column of a table. How can I add 4 additional columns so that a total of the first 5 columns will be brought back?


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900