Select max of multiple columns

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

Transact-SQL (2000)

Select max of multiple columns

Author

Topic

tmoisan
Starting Member

5 Posts

Posted - 2004-09-02 : 13:41:37

I have a table that has 3 date columns in it, let's say date1, date2 and date3. I am looking for a function of some sort that will give me the max of those three dates. Effedtively, I want a max funciton to go across multiple columns, not just a single column.

I am also looking for a similar function for integers. I have TestScore1, TestScore2 and TestScore3. I want ot pull which was the highest score of the three tests for an individual person.

Any help is appreciated.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 13:46:05

[code]
declare @myTable table (date1 datetime, date2 datetime, date3 datetime)

Insert Into @myTable
Select '1/1/2004', '2/1/2004', '3/1/2004'
Union All Select '1/2/2004', '2/3/2004', '3/4/2004'

Select maxDate = max(case when date1>date2 and date1>date3 then date1 when date2>date1 and date2>date3 then date2 else date3 end) From @myTable
[/code]

Corey

tmoisan
Starting Member

5 Posts

Posted - 2004-09-02 : 13:48:22

Thanks for youe quick rsposne, however, I am trying to avoid a temp table variable. Also, there may at some point be Date4 and I do not want to have to rewrite the procedure. There is no function that can give me the max of a list of values?

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-02 : 13:48:27

DDL and sample data will help us help you.

BUT I would assume it's not just

SELECT Student, Test, MAX(score1), MAX(score2), MAX(score3)

Brett

8-)

tmoisan
Starting Member

5 Posts

Posted - 2004-09-02 : 13:58:47

Ok, here is the scenario. I have a bunch of orders that have multiple dates including order_date, ship_by_date, change_date, return_date, receive_date, etc. I have been asked to wrie a report that includes all of the other columns in the order table and the last date the order was touched as a single column output. So, they want ot know whether the order_date, ship_by_date, Change_date, return_date or receive_date is the maximum date on the order. THey only want the max one, not the rest. so i am basically looking for something like this (assuming the max function could take more than one input)

SELECT
order_id,
customer_id,
order_cost,
MAX(order_date, ship_by_date, Change_date, return_date,
receive_date) AS LastTouchDate
FROM
order
WHERE
order_id = @orderID

Additionally, I have three types of shipping charges: Estimated, Actual, Contractual. I have to return which is the largest of the three.

SELECT
order_id,
MAX(Estimated, Actual, Contractual) AS ShipRate
FROM
order
WHERE
order_id = @orderID

Hope this helps.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-02 : 14:05:40

I see said the blindman...wait..that's another forum..

http://www.dbforums.com/member.php?u=31890


USE Northwind
GO

CREATE PROC mySproc99 @TABLE_NAME sysname, @DATA_TYPE varchar(15)
AS
DECLARE @SQL varchar(8000)
SELECT @SQL = ''
SELECT @SQL = @SQL + 'SELECT MAX(' + COLUMN_NAME + ') AS Col1 FROM ' 
	+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' UNION ALL '
  FROM INFORMATION_SCHEMA.Columns
 WHERE TABLE_NAME = @TABLE_NAME AND DATA_TYPE = @DATA_TYPE
SELECT @SQL='SELECT MAX(Col1) AS MAX_' + @DATA_TYPE + ' FROM ('+LEFT(@SQL,LEN(@SQL)-10)+') AS XXX'
EXEC(@SQL)
GO

EXEC mySproc99 'Orders', 'datetime'
GO

DROP PROC mySproc99
GO

Brett

8-)

tmoisan
Starting Member

5 Posts

Posted - 2004-09-02 : 14:24:14

very clever procedure. however, there are some date columns in the table that need NOT be calculated this way and they are showing up. also, I have to select other columns along with this as a single output statement so I am not sure this is the solution. I have alos heard that dynamic sql is very resource intensive and this is a report that will be hit by 100 people about 50 or 60 times a day.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 16:52:47

quote:
Originally posted by tmoisan

Thanks for youe quick rsposne, however, I am trying to avoid a temp table variable. Also, there may at some point be Date4 and I do not want to have to rewrite the procedure. There is no function that can give me the max of a list of values?

I think you are missing the point. I use the table variable to create examples. You would have to adapt this to your data. You can expand this to whatever you need.

By the way, I like this solution better:


declare @myTable table (date1 datetime, date2 datetime, date3 datetime)

Insert Into @myTable
Select '1/1/2004', '2/1/2004', '3/1/2004'
Union All Select '1/2/2004', '2/3/2004', '3/4/2004'


Select maxDate = max(maxDate) 
From 
	(
	Select maxDate = date1 From @myTable
	Union All Select maxDate = date2 From @myTable
	Union All Select maxDate = date3 From @myTable
	) as A

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 17:10:21

So i guess that leads to:


SELECT
	order_id,
	customer_id,
	order_cost,
	Maxdate AS LastTouchDate
FROM order
Left Join 
	(
	Select maxDate = max(maxDate) 
	From 
		(
		Select maxDate = date1 From order
		Union All Select maxDate = date2 From order
		Union All Select maxDate = date3 From order
		) as A
	WHERE order_id = @orderID
	) as B
WHERE 
order_id = @orderID

Corey

enrique
Starting Member

7 Posts

Posted - 2004-09-02 : 17:14:00

Here is something I did:

(SELECT DISTINCT SY27007 PROJECT, MAX(SY27002) sequence
FROM SY270200 GROUP BY SY27007)

SY270200 is my invoice numbering file.
SY2707 is the project number and SY2702 is the invoice number assigned so with this I am getting the latest invoice in the file per project.

I hope this can help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources