Max date from three different columns

I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
EricAre all these columns in the one table? If so, you want to unpivot:
select
max (case x.seq
when 1 then Col1
when 2 then Col2
when 3 then Col3
end)
from
(
select 1 union all
select 2 union all
select 3
) x (seq)
cross join
MyTable
If these are across 3 tables, try:
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
Eric|||> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
Eeks, is this supposed to be a cross join? How are these three tables
related? Did you mean to query against columns in three different tables,
or against three different columns in the same table? Did you want the MAX
date from any of the three columns in ALL the rows of the table, or the
greater of the three column values in every row in the table?
Can you provide decent requirements so that we don't have to ask 40
questions to figure out what you're talking about? Please see
http://www.aspfaq.com/5006|||select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
BEAUTIFUL!!!1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> select
> max (case x.seq
> when 1 then Col1
> when 2 then Col2
> when 3 then Col3
> end)
> from
> (
> select 1 union all
> select 2 union all
> select 3
> ) x (seq)
> cross join
> MyTable
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||What is it that you're really trying to achieve? We're seeing cross joins
over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
of sample data + desired results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||Well, cross joins certainly aren't the answer. If you can't produce a
simplified DDL then we can't be of much help.
Help us help you.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:u9HCy3mXGHA.1200@.TK2MSFTNGP03.phx.gbl...
I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||I would use a case expression:
case
when b.date1<=a.date1 and c.date1<=a.date1 then a.date1
when a.date1<=b.date1 and c.date1<=b.date1 then b.date1
when b.date1<=c.date1 and a.date1<=c.date1 then c.date1
end
BTW, as a rule of thumb, use UNION ALL, not UNION whenever possible -
usually performs better.|||CREATE TABLE C1(F DATETIME)
INSERT INTO C1 SELECT '2005-04-05'
CREATE TABLE C2(F DATETIME)
INSERT INTO C2 SELECT '2004-02-01'
CREATE TABLE C3(F DATETIME)
INSERT INTO C3 SELECT '2006-4-13'
SELECT MAX(X.F) AS F FROM
(
SELECT MAX(C1.F) AS F FROM C1
UNION ALL
SELECT MAX(C2.F) AS F FROM C2
UNION ALL
SELECT MAX(C3.F) AS F FROM C3
) X
DROP TABLE C1
DROP TABLE C2
DROP TABLE C3
"Eric Stott" wrote:

> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>
>