Min value across columns

Question

Post reply

Min value across columns

Michael David

Right there with Babe

Points: 788
More actions
October 16, 2006 at 2:58 pm

#76600

Is there an elegant way to derive the minimum value across columns for a row. So for example a table with multiple date columns(Date1, Date2, Date3,...), I need to get the minimum date in these columsn for every record. The function Minworks on a single column across Rows.
I like to have a set based solution. Writing a function to do this wil be cause a performance problem since this is a large table.
Thanks in advance.
Michael.

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How many columns are you talking here? Are they all datetime datatype?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Michael David Right there with Babe Points: 788 More actions · Answer 2

There are currently 8 Datetime columns.

If there is no easy way to do this, I am planning to calculate the minimum datetime for every record as it is inserted/updated.

Michael.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think id' try to use a subselect and max function:

SELECT PKColumn,othercolumns,MAX(DerivedDate) from

sometable

left outer join

(select PKColumn,DateColumn1 as DerivedDt from sometable UNION

select PKColumn,DateColumn2 as DerivedDt from sometable UNION

select PKColumn,DateColumn3 as DerivedDt from sometable UNION

select PKColumn,DateColumn4as DerivedDt from sometable UNION

...)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Rowan SSC Guru Points: 56440 More actions · Answer 4

I was thinking about using the same approach as Lowel with regards to the derived table using UNION. You did mention that your table is large so you will need to make sure that each SELECT statement in the UNION is as efficient as possible (CLUSTERED INDEX SEEK if possible). Since you have 8 columns you need to look at, your derived table will run 8 SELECTs against your table so it needs to be efficient so that this one query does not kill your performance.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ken McKelvey SSCoach Points: 18842 More actions · Answer 5

Something like the following should be more efficient as the table will only be scanned once:

-- Test Data

DECLARE @t TABLE

(

TID INT NOT NULL PRIMARY KEY

,TDate1 DATETIME NULL

,TDate2 DATETIME NULL

,TDate3 DATETIME NULL

,TDate4 DATETIME NULL

,TDate5 DATETIME NULL

,TDate6 DATETIME NULL

,TDate7 DATETIME NULL

,TDate8 DATETIME NULL

)

INSERT @t

SELECT 1, '20061017', '20061012', '20061021', '20041017', '20061017', '20061117', '20071017', '20101017'

UNION ALL

SELECT 2, '20061017', NULL, '20061021', '20041017', '20061017', '20061117', '20071017', '20031217'

-- Query ignoring NULLs

-- If want to consider NULLs, replace TDate1 etc with ISNULL(TDate1, CAST('19000101' AS DATETIME)) etc

SELECT T.TID

,MIN (

CASE N.Num

WHEN 1 THEN TDate1

WHEN 2 THEN TDate2

WHEN 3 THEN TDate3

WHEN 4 THEN TDate4

WHEN 5 THEN TDate5

WHEN 6 THEN TDate6

WHEN 7 THEN TDate7

WHEN 8 THEN TDate8

END ) As MinDate

FROM @t T

CROSS JOIN (

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8) N (Num)

GROUP BY T.TID

Michael David Right there with Babe Points: 788 More actions · Answer 6

Thank you for all the possible solutions. I will try them all out to see which is more efficient.

thanks

Michael.