Sorting across columns

  • I have a sample table like the following way:

    Col1     col2     Col3

    ------------------------

    3         4        6

    7         4        7

    8         7        2

    5         7        9

    6         6        6

    After the sorting, I should produce the result like the following:

    Col1     col2     Col3

    ------------------------

    5         7        9

    8         7        2

    7         4        7

    6         6        6

    3         4        6

    Means that I have to sort the rows for the largest value among all three column whichever come first. I saw people doing this using the command like the following in mySQL, I doubt that how could we do this in Ms SQL Server.

    http://www.issociate.de/board/post/224328/Advanced_Sorting_SQL_command.html

    Any idea or command would help, thanks

  • You can accomplish that by adding a COMPUTED column and then sorting on that i.e. COL4 with a DEFAULT VALUE of Col1 + Col2 + Col3 and then ORDER BY COL4....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • I ran across the following newsgroup discussion regarding SQL's lack of LEAST and GREATEST functions. Perhaps this will better answer your question:

    http://groups.google.com/group/mailing.database.pgsql-sql/browse_thread/thread/a6386aca9b0a55ce/81a9b83e8de82183?lnk=st&q=sql+least&rnum=1&hl=en#81a9b83e8de82183

  • If it is only 3 columns (bet it is not though )

    CREATE TABLE #temp (Col1 int,col2 int,Col3 int,Key1 int,Key2 int,Key3 int)

    INSERT INTO #temp SELECT Col1,Col2,Col3,Col1,Col2,Col3 FROM

    UPDATE #temp SET Key2=Key3,Key3=Key2 WHERE Key3 > Key2

    UPDATE #temp SET Key1=Key2,Key2=Key1 WHERE Key2 > Key1

    UPDATE #temp SET Key2=Key3,Key3=Key2 WHERE Key3 > Key2

    SELECT Col1,Col2,Col3 FROM #temp ORDER BY Key1 DESC,Key2 DESC,Key3 DESC

    DROP TABLE #temp

    Far away is close at hand in the images of elsewhere.
    Anon.

  • You do not need a temporary table...this can be achieved by doing conditional ordering in the order by clause, example:

    CREATE TABLE TESTCASE (ROW_NUM INT IDENTITY(1,1), COL1 INT, COL2 INT, COL3 INT)

    INSERT INTO TESTCASE VALUES (3,4,6)

    INSERT INTO TESTCASE VALUES (7,4,7)

    INSERT INTO TESTCASE VALUES (8,7,2)

    INSERT INTO TESTCASE VALUES (5,7,9)

    INSERT INTO TESTCASE VALUES (6,6,6)

    GO

    SELECT COL1, COL2, COL3

    FROM TESTCASE

    ORDER BY

    (CASE WHEN COL1 >= COL2 AND COL1 >= COL3 THEN COL1

          WHEN COL2 >= COL3 AND COL2 >= COL1 THEN COL2

          WHEN COL3 >= COL1 AND COL3 >= COL2 THEN COL3

     ELSE NULL

    END) DESC, COL1 + COL2 + COL3 DESC

    GO

    --Output

    Col1     col2     Col3

    ------------------------

    5         7        9

    8         7        2

    7         4        7

    6         6        6

    3         4        6

  • The computed sum column example a couple of postings above may give an incorrect sort order depending on the data.

    The ORDER BY CASE example works wonderfully for 3 columns.  It does get a wee bit complicated when more columns are added.  I can't imagine what it would look like for more than about 5 columns.

    The following will allow expandability up to 250 columns or so (the max number of UNIONs allowed in a single SELECT) simply by adding another UNION ALL SELECT for each additional column...

    --===== If experimental table exists, drop it

         IF OBJECT_ID('TempDB..#MyExample') IS NOT NULL

            DROP TABLE #MyExample

    --===== Supress the auto-display of rowcounts for appearance and speed

        SET NOCOUNT ON

    --===== Create and populate an experimental table

     CREATE TABLE #MyExample

            (RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

             Col1 INT, Col2 INT, Col3 INT, Col4 INT, Col5 INT)

     INSERT INTO #MyExample VALUES (3,4,6,4,5)

     INSERT INTO #MyExample VALUES (7,4,7,3,2)

     INSERT INTO #MyExample VALUES (8,7,2,5,6)

     INSERT INTO #MyExample VALUES (5,7,9,2,3)

     INSERT INTO #MyExample VALUES (6,6,6,5,4)

    --===== Do the cross column sort

     SELECT t.*

       FROM #MyExample t,

            (-------------------------------------------------------

              SELECT d.RowNum, MAX(Col) AS MyMax, SUM(Col) AS MySum

                FROM

                    (------------------------------------------

                     SELECT RowNum,Col1 AS Col FROM #MyExample

                     UNION ALL

                     SELECT RowNum,Col2 AS Col FROM #MyExample

                     UNION ALL

                     SELECT RowNum,Col3 AS Col FROM #MyExample

                     UNION ALL

                     SELECT RowNum,Col4 AS Col FROM #MyExample

                     UNION ALL

                     SELECT RowNum,Col5 AS Col FROM #MyExample

                    ) d ---------------------------------------

              GROUP BY d.RowNum

            ) d1 ---------------------------------------------------

     WHERE t.RowNum = d1.RowNum

     ORDER BY d1.MyMax DESC, d1.MySum DESC

    Of course, I'm using an IDENTITY column to simulate a primary key of which no table is complete without.

    Using the same data as above...

    --===== This produces the WRONG answer and simulates having a "computed sum" column

     SELECT *

       FROM #MyExample

      ORDER BY Col1+Col2+Col3+Col3+Col5 DESC

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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