Click here to Skip to main content
Rate this: bad
good
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')))
 
UPDATE:
OP has posted his resolution as an answer.
Posted 20-Jan-11 2:42am
Edited 20-Jan-11 18:34pm
v2
Comments
Sandeep Mewara at 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
good
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.
  Permalink  
Comments
SAKryukov at 20-Jan-11 12:54pm
   
A 5. What is was? Idea of relational abuse?
Rate this: bad
good
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.
  Permalink  
Comments
SAKryukov at 20-Jan-11 12:56pm
   
Makes sense - my 5. The OP's idea was relational abuse, I would say.
Rate this: bad
good
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.
 
Cheers.
  Permalink  
Rate this: bad
good
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;
Exec(@Sql);
  Permalink  
Comments
Sandeep Mewara at 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?

tx

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

  Print Answers RSS
0 OriginalGriff 205
1 Sergey Alexandrovich Kryukov 185
2 jlopez788 144
3 Rob Philpott 100
4 Prakriti Goyal 97
0 OriginalGriff 6,837
1 Sergey Alexandrovich Kryukov 5,589
2 Maciej Los 3,479
3 Peter Leow 3,323
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 28 Jun 2012
Copyright © CodeProject, 1999-2014
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