find the lastest date among many fields

I have seven date fields, and I need to select the lastest date among the
seven fields.
Example
table1
ID, date1, date2, date3, date4,date5, date6, date7
Result should show '10/07/2005'
Thanks,
Culam
CREATE TABLE table1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO table1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')7 date columns in a row looks suspiciously like poor normalization.
Anyway, try the following. Nulls will be ignored.
SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1
David Portas
SQL Server MVP
--|||Culam,
I'm not a design expert but I think you may need to work on normalizing this
a bit.
You could try something like:
SELECT ID,
(SELECT MAX(DATEVALUE)
FROM
(SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
SELECT DATE2 FROM TABLE1 UNION ALL
SELECT DATE3 FROM TABLE1 UNION ALL
SELECT DATE4 FROM TABLE1 UNION ALL
SELECT DATE5 FROM TABLE1 UNION ALL
SELECT DATE6 FROM TABLE1 UNION ALL
SELECT DATE7 FROM TABLE1)
AS Z)
FROM TABLE1
or use the MAX function with a temp table.
HTH
Jerry
"culam" <culam@.discussions.microsoft.com> wrote in message
news:49405690-F7F1-4E6A-BCCD-27407C0E35E7@.microsoft.com...
>I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Try,
CREATE TABLE t1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO t1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')
select [id], max(c1)
from
(
select [id], date1
from t1
union all
select [id], date2
from t1
union all
select [id], date3
from t1
union all
select [id], date4
from t1
union all
select [id], date5
from t1
union all
select [id], date6
from t1
union all
select [id], date7
from t1
) as t2([id], c1)
group by [id]
-- or
select
[id],
max(
case t2.c1
when 1 then date1
when 2 then date2
when 3 then date3
when 4 then date4
when 5 then date5
when 6 then date6
when 7 then date7
end
)
from t1 cross join (select 1 as c1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7)
as t2
group by [id]
drop table t1
go
AMB
"culam" wrote:

> I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Jerry Spivey wrote:
> SELECT ID,
> (SELECT MAX(DATEVALUE)
> FROM
> (SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
> SELECT DATE2 FROM TABLE1 UNION ALL
> SELECT DATE3 FROM TABLE1 UNION ALL
> SELECT DATE4 FROM TABLE1 UNION ALL
> SELECT DATE5 FROM TABLE1 UNION ALL
> SELECT DATE6 FROM TABLE1 UNION ALL
> SELECT DATE7 FROM TABLE1)
> AS Z)
> FROM TABLE1
>
Did you try that with more than one row of dates? If Culam wants just a
single maximum date then you may as well remove the outer part of the
query.
David Portas
SQL Server MVP
--|||Was just about to post how your code rocks and mine is would be sluggish
with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
Thanks for the follow up...always learning from you.
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
> Jerry Spivey wrote:
> Did you try that with more than one row of dates? If Culam wants just a
> single maximum date then you may as well remove the outer part of the
> query.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
Might want to change it up a bit for multiple rows to something like this:
SELECT TOP 1 X.ID, MAX(X.MAXDATE) AS 'MAX DATE'
FROM
(SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1) AS X
GROUP BY X.ID
ORDER BY MAX(X.MAXDATE)DESC
Thoughts?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23xMgPgpzFHA.2792@.tk2msftngp13.phx.gbl...
> Was just about to post how your code rocks and mine is would be sluggish
> with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
> Thanks for the follow up...always learning from you.
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
>|||Just my usualy caveats around TOP: Use an ORDER BY that's guaranteed to
be unique or specify TOP WITH TIES. Random results due to ties can be a
lot of hassle.
David Portas
SQL Server MVP
--