Click here to Skip to main content
12,816,775 members (34,878 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL-Server
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.
Posted 20-Jan-11 3:42am
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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Columns have no specific order, so retrieving them by index would be a) pointless, and b) unpredictable.
SAKryukov 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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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.
SAKryukov 20-Jan-11 12:56pm
Makes sense - my 5. The OP's idea was relational abuse, I would say.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

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.

Rate this: bad
Please Sign up or sign in to vote.

Solution 4

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;
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)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170308.1 | Last Updated 28 Jun 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100