ORDER BY earliest date in a row -- sql server programming

Alejandro Mesa
9/16/2003 3:32:12 PM

This is a very low performance solution.

Example:

create table table1(
c1 int not null identity primary key,
c2 datetime null,
c3 datetime null,
c4 datetime null,
check (coalesce(c2, c3, c4) is not null)
)
go

create view vwTable1
as
select a.c1,
min(
case=20
when b.c1 =3D 'c2' then a.c2=20
when b.c1 =3D 'c3' then a.c3=20
when b.c1 =3D 'c4' then a.c4=20
end) as c2
from table1 a,=20
(
select column_name=20
from information_schema.columns=20
where table_name =3D 'table1' and=20
column_name in ('c2', 'c3', 'c4')
)b(c1)=20
where case=20
when b.c1 =3D 'c2' then a.c2=20
when b.c1 =3D 'c3' then a.c3=20
when b.c1 =3D 'c4' then a.c4=20
end is not null
group by a.c1
go

set nocount on
insert into table1 values(getdate(), getdate() + 1, getdate() + 2)
insert into table1 values(getdate() - 1, null, getdate() + 2)
insert into table1 values(getdate() + 1, getdate() - 2, null)
insert into table1 values(null, getdate() + 4, getdate() + 2)
insert into table1 values(getdate() - 6, null, null)
insert into table1 values(null, null, getdate() - 12)
set nocount off
go

select c1, cast(c2 as varchar), cast(c3 as varchar),cast(c4 as varchar)
from table1 a
order by (select v.c2 from vwTable1 v where v.c1 =3D a.c1)
go

drop view vwTable1
drop table table1
go

Result:

c1 =
=20
----------- ------------------------------ =
------------------------------ ------------------------------=20
6 NULL NULL =
Sep 4 2003 3:30PM
5 Sep 10 2003 3:30PM NULL =
NULL
3 Sep 17 2003 3:30PM Sep 14 2003 3:30PM =
NULL
2 Sep 15 2003 3:30PM NULL =
Sep 18 2003 3:30PM
1 Sep 16 2003 3:30PM Sep 17 2003 3:30PM =
Sep 18 2003 3:30PM
4 NULL Sep 20 2003 3:30PM =
Sep 18 2003 3:30PM

(6 row(s) affected)

Steve Kass
9/16/2003 3:46:51 PM

[sent to comp.databases.ms-sqlserver 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

[quoted text, click to view]

Yannick Turgeon
9/16/2003 3:47:02 PM

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

[quoted text, click to view]

Tom Moreau
9/16/2003 4:10:55 PM

Here's one solution (assumes you have 3 such columns but is easily =
modifed:

select
t1.*
, z.earliest
from
@t as t1
join
(
select
id
, min(col) as earliest
from
(
select
t1.id
, case x.col
when 1 then t1.col1
when 2 then t1.col2
when 3 then t1.col3
end as col
from
@t as t1
cross join
(
select 1 as col
union all
select 2
union all
select 3
) as x
) as y
group by
id
) as z on z.id =3D t1.id

order by

t1.id

--=20
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

[quoted text, click to view]
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 =3D MT.RecordID
ORDER BY T.MinDate

HTH

Yannick

[quoted text, click to view]
way
of
[quoted text, click to view]

Jon Maz
9/16/2003 8:27:34 PM

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

Simon Hayes
9/16/2003 9:38:32 PM

[quoted text, click to view]

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

David Portas
9/16/2003 10:06:50 PM

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

Jon Maz
9/18/2003 4:58:51 PM

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : ORDER BY earliest date in a row