is there a way to select column data for columns with an ordinal position < a particular ordinal position?

Question

is there a way to select column data for columns with an ordinal position < a particular ordinal position?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
September 13, 2016 at 5:46 pm

#313224

Is there a way to select column data for columns with an ordinal position < a particular ordinal position? I'm considering using dynamic sql to return data from 10 different tables but I want to exclude the last 4 columns (created/updated) from the select statement. So maybe something like this:
declare @maxordinal int
select @maxordinal getordinal(createdate) - 1 from mytable
select * from mytable where getordinal(getcolumn) < @maxordinal
Is there a way to do this or an alternative way to accomplish what I'm trying to do here?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Why not just create a table that contains the column names of <mytable> and their ordinal position. You could name it <mytable>_cols. Then use some logic in your dynamic SQL that looks like:

...

FROM <mytable>_cols

WHERE col_ordinal <= @maxordinal;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 2

too much overhead I was wondering if there was a simple way

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Actually, that table is already created for you by the nice (shoosh you there! :-)) guys from MS:

declare @maxordinal int

select @maxordinal = column_id - 1

from sys.columns

WHERE OBJECT_ID = OBJECT_ID('mytable')

AND name = 'createdate'

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 4

They even created the "getordinal" function for you:

SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'ColumnId')

_____________
Code for TallyGenerator

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

And none of this will work when the table schema changes and the new column gets a higher ordinal position.

Shamless self promotion - read my blog http://sirsql.net

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Nicholas Cain (9/14/2016)
And none of this will work when the table schema changes and the new column gets a higher ordinal position.

+1000

OP - you should try to avoid using ordinal position of columns for anything. The order of columns in a table should not make any difference because they should always be addressed by name.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101046 More actions · Answer 7

It sounds like you have typical "createdate", "createuser", 'updatedate", "updateuser" that you want to exclude.

But do it by column name rather than position. Those won't necessarily be the last columns in a table, even if most often you make sure they are.

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".