January 9, 2009 at 3:25 am
Hi
I have table it consist of 100 columns. I want to fetch
2,3,24,1 restof the columns
How it is possible plz reply.
I don't want to mension all the columns in query
With Regards
Lee
January 9, 2009 at 3:33 am
Specify the column number instead of name.
Select 1,3,24 from yourtablename
-Vikas Bindra
January 9, 2009 at 3:36 am
it is not working it displays
all rows as 1,2,4
January 9, 2009 at 3:37 am
Let say your table has 100 rec and the ID column withvalues 1,2,3,4,5...100
so you write
SELECT * FROM TABLE WHERE ID IN (2,3,24,1)
Post more info for your problem!
:hehe:
January 9, 2009 at 3:39 am
Invalid column name 'ID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID'.
January 9, 2009 at 3:44 am
Simple answer is you must name the columns explicitly in the select statement.
You can use something like tablename.* (or correlation_name.*) to say all columns from one table, similarly you could do:
select cola, colx, colz, t.* from table t
Which would give you columns cola, colx and colz at the beginning, but they would also be included later in the list of columns as well, and adding an order by cola will cause an error.
Far better to name the columns explicitly - just drag them as required (or all together) from the object explorer if you do not want to type them out.
It is generally considered bad practice to use * in production code anyway for maintainability and performance reasons.
Last option - you could create a view with the columns in the order you want them and select from that. but the view will contain the select with all the required column names.
Mike
January 9, 2009 at 3:47 am
SELECT your column-delimited list of columns here
FROM yourtable
There isn't an easy way to do this, Lee. It can be done by gathering the column list for your table from syscolumns but that's going to be far more effort than manually building your column list.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 9, 2009 at 9:52 am
Mike and Chris are absolutely correct. You need to explicitly list all columns that you want to select in the SELECT clause of your statement. This is just how SQL works. Then use a view or stored procedure to store the query so you don't hvae to re-type it each time you want that same list again.
Mike, just curious, but can you explain your comment regarding the error caused by adding the column in an ORDER BY clause? Ordering a query by a single column should not be a problem and should certainly not create an error. Am I missing something?
January 9, 2009 at 10:46 am
Hi John,
Only something I hit with 2005. Prior to that you coukld do ..
select * from foo order by bah
and then change to..
select bah, * from foo order by bah
(typically in Query analyser for example because it may have been easier to look at what was in column bah adjacent to the first column)
and at 2000 that was fine. However from 2005 if the same column is in the resultset more than once and you include in an order by you get an error telling you that the name is ambiguous. This is I suppose fair in many ways, sionce the name is effectively ambiguous.
Hope that makes sense.
Mike
January 9, 2009 at 10:50 am
I was missing something! I did not notice that you had both the column name and *.
Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply