Using TOP To Rank Columns In a Table

  • Tim Parker

    SSCommitted

    Points: 1644

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

    😀

  • Carlo Romagnano

    SSC-Insane

    Points: 21692

    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

    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

    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.

  • kevriley

    SSCrazy Eights

    Points: 8907

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

  • sanjay.patel 30254

    SSC Enthusiast

    Points: 101

    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

    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: 3451

    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: 124967

    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: 1007

    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

    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: 124967

    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

    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

    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

    SSCoach

    Points: 19793

    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