Using TOP To Rank Columns In a Table

Question

Post reply

Using TOP To Rank Columns In a Table

Tim Parker

SSCommitted

Points: 1644
More actions
October 24, 2011 at 10:28 pm

#246936

Comments posted to this topic are about the item Using TOP To Rank Columns In a Table
😀

Viewing 15 posts - 1 through 15 (of 20 total)

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

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 1

CELKO (10/24/2011)
Why did you encourage him? Sounds like time for re-education:w00t:

I agree.

back_of_the_fleet Say Hey Kid Points: 683 More actions · Answer 2

Regardless of whether or not this is a "bright idea", it's a little worrying that the developer did not know enough to approach the problem for themself, or have the nouse to research it.

John Rogerson
BI Technical Lead
Clear Channel International

adam.everett SSC Veteran Points: 265 More actions · Answer 3

if was just a means to an end then just do a SELECT * FROM and dont bother with the schema extract.

Cringe, really bad a * but so.

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 4

Kev Riley

SSCrazy Eights

Points: 9075

October 25, 2011 at 2:06 am

#1398983

What was the answer to the question 'why?'?

sanjay.patel 30254 SSC Enthusiast Points: 101 More actions · Answer 5

Excellent, removes the need for Cursors, and is very flexible to build up a totally dynamic Select clause that is very very useful indeed...

More like this please.....

Alberto Zoppo Valued Member Points: 73 More actions · Answer 6

For select dthe columns without remove last comma

SELECT TOP 10 @columnName = COALESCE(@columnName + ',' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tableName

AND TABLE_SCHEMA = 'DBO'

ORDER BY ORDINAL_POSITION ASC

thisisfutile Hall of Fame Points: 3668 More actions · Answer 7

I've been coming to these forums long enough to know that Joe Celko was going to respond to this one. I recall Joe discussing the TOP clause in another forum long ago. Correct me if I'm wrong Joe, but TOP isn't to be trusted to pull the "first rows in a table" because tables don't store rows in any particular order. Is that what you were going for in your re-education reprimand? I've always felt it was a little misleading...it should have been called LIMIT or something like that because that's all it's really doing...limiting the result set.

IMHO.

Gabe

Eric M Russell SSC Guru Points: 125519 More actions · Answer 8

CELKO (10/24/2011)
Why did you encourage him? Sounds like time for re-education:w00t:

On a slow day, even the best of us may become so distracted by the challenge of solving some small puzzle that we forget to probe into why the puzzle was there to begin with.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

John_Hill Ten Centuries Points: 1173 More actions · Answer 9

Yeah, I'm with most others. I find the question more interesting that the solution. Why was it he needed that?

sanjay.patel 30254 SSC Enthusiast Points: 101 More actions · Answer 10

Have you ever worked up a example to display dynaimc source to destination data column mapppings using the same technique, and also the matching of data items from source column to destination columns, basically a frgament of code that replicates the SSIS data mapper, removing the need to map source to destination in SSIS...?:-)

Eric M Russell SSC Guru Points: 125519 More actions · Answer 11

sanjay.patel 30254 (10/25/2011)
Have you ever worked up a example to display dynaimc source to destination data column mapppings using the same technique, and also the matching of data items from source column to destination columns, basically a frgament of code that replicates the SSIS data mapper, removing the need to map source to destination in SSIS...?:-)

In one of the datamarts I work with, the ordinal position of columns between Dev, QA, and Production are different. What I've done is create a view for each table that gets bulk inserted, and then I reference the view in SSIS or BCP. I've found that to be easier and more maintainable than fooling with the data mapper or bcp format files.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sanjay.patel 30254 SSC Enthusiast Points: 101 More actions · Answer 12

What I would like to see is a source to destination dynamic data mapper, which is all referenced by using the dynaim columns as in the exmaple you have used, only extended to to also reference a second table, which agin the ordinal positions are sorted to relfect the data mapping, and then having further SQL conditions tagged on to allow a filter to be put in place.

I want two tables, ordinal position to relfect the data mapping sequence, the source and destination columns and condition blocks may exist in either table, a dynamic process to put it all together, just the SQL part, don't want to execute it.....:-P

Tim Parker SSCommitted Points: 1644 More actions · Answer 13

thisisfutile (10/25/2011)
I've been coming to these forums long enough to know that Joe Celko was going to respond to this one. I recall Joe discussing the TOP clause in another forum long ago. Correct me if I'm wrong Joe, but TOP isn't to be trusted to pull the "first rows in a table" because tables don't store rows in any particular order. Is that what you were going for in your re-education reprimand? I've always felt it was a little misleading...it should have been called LIMIT or something like that because that's all it's really doing...limiting the result set.
IMHO.
Gabe

I agree TOP can't be trusted to pull the first rows in a table, that's why I included the caveat of ORDER BY Ordinal_Position in the filter.

😀

Phil Factor SSC-Insane Points: 20244 More actions · Answer 14

I am intrigued as to why anyone would need this technique in a normalised database. Was this developer wrestling with a pivot table by any chnce that had been saved as a table?

Best wishes,
Phil Factor