ORDER BY earliest date in a row

Hi,

I have a table (SQL Server 2000) with several date columns in it, all of
which are individually NULLable, but in any one row, not all the dates can
be NULL.

I want a query which ORDERs BY the earliest date it finds in each row. I'm
guessing I have to do this in two steps:

STEP 1
Using a UDF, find the earliest date and stick it in a new calculated
column "earliest date"

STEP 2
ORDER BY this UDF-created column

If this is the right way to go about this, is there a simple SQL way of
determining which is the lowest of several dates? (ie of doing STEP 1).

Or am I looking at this the wrong way, and missing an easy *one-step* way of
getting what I want?

TIA,

JON"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bk7kdg$gva$1@.online.de...
> Hi,
> I have a table (SQL Server 2000) with several date columns in it, all of
> which are individually NULLable, but in any one row, not all the dates can
> be NULL.
> I want a query which ORDERs BY the earliest date it finds in each row.
I'm
> guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new
calculated
> column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
> If this is the right way to go about this, is there a simple SQL way of
> determining which is the lowest of several dates? (ie of doing STEP 1).
> Or am I looking at this the wrong way, and missing an easy *one-step* way
of
> getting what I want?
> TIA,
> JON

This is probably easier to do in a client/reporting tool than in pure SQL,
but one possible solution is as follows (performance won't be good on a
large table):

create view dbo.DateCols
as
select KeyCol, DateCol1 as 'DateCol'
from dbo.MyTable
union
select KeyCol, DateCol2
from dbo.MyTable
union
select KeyCol, DateCol3
from dbo.MyTable

select t.*
from dbo.MyTable t
join
(
KeyCol, min(DateCol) as 'MinDate'
from dbo.MyTable
group by KeyCol
) as dt
on t.KeyCol = dt.KeyCol
order by dt.MinDate

Simon|||[sent to microsoft.public.sqlserver.programming separately - newsreader can't
sent to 2 news servers at once.]

Jon,

Here is another option, using Alejandro's definitions (thanks, Alejandro!)

create view vwTable1A
as
select table1.c1,
case n when 2 then c2 when 3 then c3 when 4 then c4 end c,
case n when 2 then '2' when 3 then '3' when 4 then '4' end i
from table1, (
select 2 n union all select 3 union all select 4
) N
go

select
c1,
(select c from vwtable1A where c1 = T.c1 and i = '2') c2,
(select c from vwtable1A where c1 = T.c1 and i = '3') c3,
(select c from vwtable1A where c1 = T.c1 and i = '4') c4
from vwtable1A T
group by c1 order by min(c)

-- Steve Kass
-- Drew University
-- Ref: 044B6F84-937C-4CDE-B9F0-BBEB959DBB7F

Jon Maz wrote:

>Hi,
>I have a table (SQL Server 2000) with several date columns in it, all of
>which are individually NULLable, but in any one row, not all the dates can
>be NULL.
>I want a query which ORDERs BY the earliest date it finds in each row. I'm
>guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new calculated
>column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
>If this is the right way to go about this, is there a simple SQL way of
>determining which is the lowest of several dates? (ie of doing STEP 1).
>Or am I looking at this the wrong way, and missing an easy *one-step* way of
>getting what I want?
>TIA,
>JON
>|||Jon,

I would try this (Air coded):

SELECT MT.*
FROM MyTable MT
INNER JOIN(
SELECT T.RecordID, MIN(T.MinDate) AS MinDate
FROM
(SELECT RecordID, Date1 AS MinDate
FROM MyTable
UNION ALL
SELECT RecordID, Date2 AS MinDate
FROM MyTable
UNION ALL
SELECT RecordID, Date3 AS MinDate
FROM MyTable
UNION ALL
... /* Any other date field in your record ... */
) AS T
GROUP BY T.RecordID) AS T ON T.RecordID = MT.RecordID
ORDER BY T.MinDate

HTH

Yannick

"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bk7kdg$gva$1@.online.de...
> Hi,
> I have a table (SQL Server 2000) with several date columns in it, all of
> which are individually NULLable, but in any one row, not all the dates can
> be NULL.
> I want a query which ORDERs BY the earliest date it finds in each row.
I'm
> guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new
calculated
> column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
> If this is the right way to go about this, is there a simple SQL way of
> determining which is the lowest of several dates? (ie of doing STEP 1).
> Or am I looking at this the wrong way, and missing an easy *one-step* way
of
> getting what I want?
> TIA,
> JON
>|||Another one (using Alejandro's DDL):

SELECT *
FROM Table1
ORDER BY
(SELECT MIN(dt)
FROM
(SELECT c2 AS dt
UNION ALL
SELECT c3
UNION ALL
SELECT c4) AS d)

--
David Portas
----
Please reply only to the newsgroup
--|||Hi,

Thanks to all for the great replies.

I ended up using a variant on David's code (because it was the shortest) and
using it in a UDF. A cut-down version of this UDF is below, and I was just
wondering if there a more succint way of writing it (ie without all the
repetition of 'WHERE CaseID=@.CaseID'?)

Cheers,

JON

__________________________________________________ __

CREATE FUNCTION EarliestDate(@.CaseID as INT, @.DateType as VarChar(255))
RETURNS DateTime
AS
BEGIN

DECLARE @.RESULT DateTime
SET @.RESULT = ''

IF @.DateType = 'NonWECLetters_SentToClient'

SELECT @.RESULT =

MIN(dt)
FROM
(SELECT DateBWSLettSentClient AS dt FROM tblCases WHERE CaseID=@.CaseID
UNION ALL
SELECT DateRMLLettSentClient FROM tblCases WHERE CaseID=@.CaseID
UNION ALL
SELECT DateBWSLettEqChSentClient FROM tblCases WHERE CaseID=@.CaseID) AS
d

RETURN @.RESULT

END
GO