SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Timothy Parker / Using TOP To Rank Columns In a Table / Latest Posts

RE: Using TOP To Rank Columns In a Table

Nick W* — Wed, 02 Nov 2011 11:00:02 GMT

This just smacks of laziness on the developers part, to go to google and look up the API of their language's SQL Data Reader object. Pretty much every DB API allows you to select things based on column ordinal. C# will even allow you to get the name of the column back. Time for clue-bat application ;-)

RE: Using TOP To Rank Columns In a Table

Resender — Thu, 27 Oct 2011 07:44:49 GMT

My question is rather why do you want the top 10 columns rather then how

RE: Using TOP To Rank Columns In a Table

Eric M Russell — Wed, 26 Oct 2011 07:06:01 GMT

I think the article presents a good enough solution to a specific problem. If I ever have a need to select the TOP X columns from a table, then it would be an ad-hoc query I need right then and there, and I'd rather use someone elses code than waste time rolling my own solution. Obviously it's something that wouldn't belong in the application.

RE: Using TOP To Rank Columns In a Table

john.martin-975670 — Wed, 26 Oct 2011 06:50:46 GMT

A minor point - the spelling of 'dyanamic'

RE: Using TOP To Rank Columns In a Table

Jeff Moden — Tue, 25 Oct 2011 18:22:58 GMT

[quote][b]sanjay.patel 30254 (10/25/2011)[/b][hr]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[/quote]Actually, this article gave you enough information to easily write your own. When you're done, you should write an article about it. ;-)

RE: Using TOP To Rank Columns In a Table

Ryan C. Price — Tue, 25 Oct 2011 13:02:05 GMT

I'm not going to judge the merits of the problem. But I am going to point out a small but important thing that I see crop up frequently:[code="plain"] '[' + COLUMN_NAME + ']'[/code]isn't a whole lot better than just COLUMN_NAME - what if they have a ']' in a column name ?[code="plain"]QUOTENAME(COLUMN_NAME,'[')[/code]is much better

RE: Using TOP To Rank Columns In a Table

Phil Factor — Tue, 25 Oct 2011 11:04:06 GMT

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?

RE: Using TOP To Rank Columns In a Table

Tim Parker — Tue, 25 Oct 2011 08:18:29 GMT

[quote][b]thisisfutile (10/25/2011)[/b][hr]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[/quote]I agree TOP can't be trusted to pull the first rows in a table, that's why I included the caveat of[b] ORDER BY Ordinal_Position [/b]in the filter.

RE: Using TOP To Rank Columns In a Table

sanjay.patel 30254 — Tue, 25 Oct 2011 08:09:08 GMT

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

RE: Using TOP To Rank Columns In a Table

Eric M Russell — Tue, 25 Oct 2011 08:00:26 GMT

[quote][b]sanjay.patel 30254 (10/25/2011)[/b][hr]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...?:-)[/quote]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.

RE: Using TOP To Rank Columns In a Table

sanjay.patel 30254 — Tue, 25 Oct 2011 07:49:14 GMT

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...?:-)

RE: Using TOP To Rank Columns In a Table

john 50727 — Tue, 25 Oct 2011 06:56:22 GMT

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

RE: Using TOP To Rank Columns In a Table

Eric M Russell — Tue, 25 Oct 2011 06:41:00 GMT

[quote][b]CELKO (10/24/2011)[/b][hr]Why did you encourage him? Sounds like time for re-education:w00t:[/quote]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.

RE: Using TOP To Rank Columns In a Table

thisisfutile — Tue, 25 Oct 2011 06:25:24 GMT

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

RE: Using TOP To Rank Columns In a Table

Alberto Zoppo — Tue, 25 Oct 2011 04:14:14 GMT

For select dthe columns without remove last commaSELECT TOP 10 @columnName = COALESCE(@columnName + ',' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME))FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @tableNameAND TABLE_SCHEMA = 'DBO'ORDER BY ORDINAL_POSITION ASC

RE: Using TOP To Rank Columns In a Table

sanjay.patel 30254 — Tue, 25 Oct 2011 02:55:59 GMT

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

RE: Using TOP To Rank Columns In a Table

kevriley — Tue, 25 Oct 2011 02:06:30 GMT

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

RE: Using TOP To Rank Columns In a Table

adam.everett — Tue, 25 Oct 2011 02:06:16 GMT

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.

RE: Using TOP To Rank Columns In a Table

back_of_the_fleet — Tue, 25 Oct 2011 01:29:17 GMT

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.

RE: Using TOP To Rank Columns In a Table

Carlo Romagnano — Tue, 25 Oct 2011 01:18:11 GMT

[quote][b]CELKO (10/24/2011)[/b][hr]Why did you encourage him? Sounds like time for re-education:w00t:[/quote]I agree.

RE: Using TOP To Rank Columns In a Table

CELKO — Mon, 24 Oct 2011 23:10:47 GMT

Why did you encourage him? Sounds like time for re-education:w00t:

Using TOP To Rank Columns In a Table

Tim Parker — Mon, 24 Oct 2011 22:28:30 GMT

Comments posted to this topic are about the item [B]Using TOP To Rank Columns In a Table[/B]