Using TOP To Rank Columns In a Table

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

    😀

  • CELKO (10/24/2011)


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

    I agree.

  • 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

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

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

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

  • 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

  • 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

  • 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

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

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

  • 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

  • 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

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

    😀

  • 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
    Simple Talk

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

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