Help me: position of column instead of name or alias column name
-
Hi guys,
I have a problem
Select * from table where col1 = 'somevalue'
My question is:
Can I use the position of col instead the name or alias
like:
Select * from table where 1 = 'somevalue' (1 is the the first column)
But running it sql query analyzer produce an error:
Do you know to do it?
-
I think it's possible if your column 1 has IDENTITY.
Then I believe you can do whatever with that.
Such as ORDER BY $IDENTITY or WHERE $IDENTITY = 'blabla'
-
the first col is with identity:
select * from table WHERE $IDENTITY = '28'
but i get the error..
Any help?
-
try
... WHERE @@IDENTITY ...
-
You could probably get an ugly solution using dynamic SQL and the information_schema.columns view, but I wouldn't recommend it. One of the many reasons I wouldn't is that if you drop column 1, there is then no column whose ordinal is 1 and you'll get an error. Another is that performance is likely to suffer.
John
-
Won't work @@Identiy is a scalar function the returns the last identity value created in ANY table by anything in the current scope chain including triggers.
John Mitchel is correct... you'll need to make some dynamic SQL to do this but I have to ask, why on Earth do you need to do this?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you want to check the identity column specifically, you can use the reserved name IDENTITYCOL, which will always refer to the identity column of any table:
SELECT * FROM tableName
WHERE identityCol = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Scott's method will certainly work! I'd still like to know why "net" wants to do this...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff
Yes, so long as he only wants to do this on the identity column. His original post was a little more general than that. We await the explanation...
John
-
Question is more general and used for any column.
It is not for me but for a my collegue...He said that he needs to use position but not column name or alias to keep the application "product oriented".
I do not know more....
thank
-
Product oriented? Sounds like management speak to me, and I don't think it translates into anything that you, as a DBA, would want to touch with the proverbial barge pole. Still, you came here for an answer, not for a lecture, so here it is:
declare @colno smallint, @tablename sysname, @columnname sysname
set @colno = 1 --enter the column number here
set @tablename = 'YourTable' --enter the table name here
select @columnname = column_name from information_schema.columns
where ordinal_position = @colno and table_name = @tablename
exec ('select * from ' + @tablename + ' where ' + @columnname + ' = 1')
You will notice that this relies on the ordinal_position value in the information_schema.columns view. This value is defined when the table is created and does not change, even when colmuns are added or deleted. Therefore, you may find that the second column in a table has ordinal_position 3 and no column has ordinal_position 2. You may wish to add some error handling logic that tells you when there is no column with the number supplied.
So use this at your own risk - in my opinion, if you don't know what the name of a column in a table is when you're writing a query, you've no business writing the query at all.
Good luck!
John
-
"So use this at your own risk - in my opinion, if you don't know what the name of a column in a table is when you're writing a query, you've no business writing the query at all."
I agree absolutely with John.
In ORDER BY it is possible to use column number, but even there it isn't a good idea to use it - you want to order by the same column even if you change the order of columns or add new column at the beginning. Always name columns explicitly in your queries.
-
Ask this joker what he means by product-oriented. It sounds as though he means 'trying to use rows as columns'.
You need to give us a little more background. Your current local problem space is non-achievement-facilitating and it would be worth mounting a localised retrogressive design audit to gain sight of any feasible outcome-superior solution path, at which point you can take a view and determine revised design scenarios which leverage enhanced technological expertise.
In normal English, if you can tell us what the wider problem you want to solve is, we may be able to suggest a way of avoiding this problem altogether.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
As far as I know you cannot refer in a where clause to a column number.
But you can do this in the order by clause:
ie:
select SomeFields from MyTable where 1='SomeValue'
would NOT work.
But:
select SomeFields from MyTable where SomeCol='SomeValue' order by 1
will work. Also you can use aliases in the order by clause:
select Field1, Field2 as F from MyTable where SomeCol='SomeValue' order by F
will work as well
Bye
Gabor
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply