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