SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query

Author

Topic

Matt Greatorex
Starting Member

2 Posts

Posted - 2005-06-29 : 12:26:01

I'm hoping this will be an easy one for someone out there, but it's got me stumped.

I have a table with a varying number of records in it. The structure is such that all columns hold a numeric value. I need to select the highest column value from each row and create a new table containing just these values.

My problem is that I don't know how to identify each highest value, given that it could be in a different column for every row.

Any suggestions gratefully received.

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-29 : 12:33:33

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Matt Greatorex
Starting Member

2 Posts

Posted - 2005-06-29 : 15:21:36

I've been told to phrase the problem differently, so I hope this is okay. Apologies for any missing info.

To create tables with a similar structure:

Create table Table1(Col1 int, Col2 int, Col3 int)
Create table Table2(Col1 int)

I'm not sure on the best way to populate Table1, but as long as there are at least three rows and the numbers are all different, it will be sufficient. Perhaps

Insert into Table1(Col1, Col2, Col3)
Values (20,30,40,50,60,70,80,90,100)

The code I'm trying to write would be something along the following lines, in the languages I can use. I just don't know how to phrase it in SQL:

Fetch First from Table1
Do while not EOT Table1

Set high_val = Col1
For col_count = 2 to 25 Do

If Col(col_count) > high_val then
high_val = Col(col_count)
End If

End For

Insert into Table2.Col1 Values(high_val)
Fetch Next from Table1

End Do

Hopefully, this will result in Table2 consisting of the same number of rows as Table1, each containing the highest value from the corresponding row in Table1 e.g. 40, 70, 100

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-29 : 15:52:05

There may be a much better way of doing this, but... I can brute force it with a temp table


-- Number the rows in a working table
SELECT IDENTITY(INT, 1,1) As TID, COL1, COL2, COL3, ... COLN
INTO #Temp
FROM Table1

-- Collapse to a single column
SELECT TID, Col1 As Col INTO #Temp2 FROM #Temp

INSERT INTO #Temp2 (TID, Col)
  SELECT TID, COL2 FROM #Temp
...
INSERT INTO #Temp2 (TID, Col)
  SELECT TID, COLN FROM #Temp -- Last column

-- Grand finale.  Get the MAX from Each Row...

SELECT MAX(Col) As MaxCol
  FROM #Temp2
  GROUP BY TID

-- Or, to insert this in another table...
INSERT INTO Table2 (Col1)
  SELECT MAX(Col) As MaxCol
    FROM #Temp2
    GROUP BY TID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources