March 1, 2011 at 7:38 am
select max(myDate) from
( select field_1 myDate from myTable
union all
select field_2 myDate from myTable
union all
select field_3 myDate from myTable) as Temp
It will return the maximum date of the three date columns values.
Example :
If u r data would be like below
select * from myTable
field_1field_2field_3
1/1/20102/2/20103/3/2010
1/1/20112/2/20113/3/2011
Query Returns :
2011-03-03
September 22, 2012 at 9:19 pm
It can also be done using Pivot Concept...very simple
SELECT Max(date)
FROM (SELECT date
FROM (SELECT date1,
date2,
date3
FROM sample) AS X
UNPIVOT (date
FOR sample IN (date1,
date2,
date3)) AS unpivoty) AS Z
September 24, 2012 at 11:46 am
I thought this would be an easy one but had to get some help too. Try this...
declare @dt table ( akey int, d1 datetime, d2 datetime, d3 datetime )
insert into @dt values (1, '2012-01-01', getdate(), '2012-09-01' )
insert into @dt values (2, getdate(), '2012-01-01', '2012-09-01' )
insert into @dt values (3, '2012-01-01', '2012-09-01', getdate() )
select
akey, max(dates)
from
( select akey, d1, d2, d3 from @dt ) p
unpivot (
dates for datevals in ( d1, d2, d3 )
)
AS unpvt
group by akey
September 24, 2012 at 12:06 pm
I thought I had posted my reply prior so I apologize if this is a duplicate post...
Here is an alternative you may want to try to get the max date value form your columns. I thought this was simple but had to get help from a peer.
declare @dt table ( akey int, d1 datetime, d2 datetime, d3 datetime )
insert into @dt values (1, '2012-01-01', getdate(), '2012-09-01' )
insert into @dt values (2, getdate(), '2012-01-01', '2012-09-01' )
insert into @dt values (3, '2012-01-01', '2012-09-01', getdate() )
select
akey, max(dates)
from
( select akey, d1, d2, d3 from @dt ) p
unpivot (
dates for datevals in ( d1, d2, d3 )
)
AS unpvt
group by akey
September 24, 2012 at 11:47 pm
--Sample Data
declare @tbl table
(
id int primary key
,field_1 date
,field_2 date
,field_3 date
)
insert into @tbl values(1,'20120823','20120824','20120825')
--Query
select max(a.mydate),a.id
from (
select id,MAX(field_1) mydate from @tbl group by id
union all
select id,MAX(field_2) from @tbl group by id
union all
select id,MAX(field_3) from @tbl group by id
)a
group by a.id
I think this is what required for you.......
So, from the next time whenever you post a query make sure that you provide sample data like above so that one can easily answer your query.........
[font="Comic Sans MS"]Vishwanath[/font]
September 24, 2012 at 11:58 pm
--sample Data
declare @tbl table
(
id int
,field_1 date
,field_2 date
,field_3 date
)
insert into @tbl values(1,'20120823','20120824','20120825')
--Query
select max(a.mydate),a.id
from (
select id,field_1 mydate from @tbl
union all
select id,field_2 from @tbl
union all
select id,field_3 from @tbl
)a
group by a.id
I think this is what you require.... Please provide some sample day from next time.. So, that it will be easy to answer
April 23, 2013 at 4:48 pm
what is differential backup .
April 23, 2013 at 4:50 pm
why is differntial backup is so important
April 23, 2013 at 5:04 pm
fadewumi (4/23/2013)
why is differntial backup is so important
Please post new questions to a new thread. This question has nothing to do with this thread.
And, fyi, a differential backup is a backup of all changes made to a database since the last full backup.
April 24, 2013 at 5:43 am
You have to alias the subquery in this case:
select max(myDate) from
( select field_1 myDate from myTable
union all
select field_2 myDate from myTable
union all
select field_3 myDate from myTable) x
April 24, 2013 at 7:26 am
My first thought would be to do the case statement shown earlier. This seems to be cleaner code if someone else has to look at it in the future.
To those that are doing the 'Union All'. If you are trying to get the max date wouldn't just using the 'Union' statement be more efficient? Since doing just 'Union' will eliminate any duplicate entries. 'Union All' puts in every entry into the result set.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
December 3, 2013 at 6:46 am
Erroneous posting, sorry!
December 3, 2013 at 10:47 am
CASE WHEN CASE WHEN date1 > date2 THEN date1 ELSE date2 END > CASE WHEN date3 > date2 THEN date3 ELSE date2 END THEN CASE WHEN date1 > date2 THEN date1 ELSE date2 END ELSE CASE WHEN date3 > date2 THEN date3 ELSE date2 END END
Far away is close at hand in the images of elsewhere.
Anon.
December 3, 2013 at 12:00 pm
the database TSQL2012 used in the following script can be downloaded at:
http://tsql.solidq.com/books/tk70461/
USE TSQL2012;
--select the grandmax of the max of three columns
DECLARE @date1Max AS datetime = (SELECT MAX([orderdate])
FROM [Sales].[OrderValues]);
DECLARE @date2Max AS datetime = (SELECT MAX([requireddate])
FROM [Sales].[OrderValues]);
DECLARE @date3Max AS datetime = (SELECT MAX([shippeddate])
FROM [Sales].[OrderValues]);
--SELECT @date1Max AS orderdate, @date2Max AS requireddate , @date3Max AS shippeddate; --TO CHECK CORRECT DATE
SELECT CASE
WHEN @date1Max >= @date2Max AND @date1Max >= @date2Max THEN @date1Max
WHEN @date2Max >= @date1Max AND @date1Max >= @date3Max THEN @date2Max
ELSE @date3Max
END AS GrandMax;
--very costly way to do the same
select max(myDate) from
( select [orderdate] myDate from [Sales].[OrderValues]
union all
select [requireddate] myDate from [Sales].[OrderValues]
union all
select [shippeddate] myDate from [Sales].[OrderValues]) uniontable ;
Two ways to obtain the same result, the first has a query cost of 27%, the second 73%, when run together, in the first there is just three simple select statements and a select statement to process a CASE statement with no cost whatsoever because it does everything in memory.
As to the efficiency of the union all against the union, the union all is more efficient because it doesn't have to filter out the duplicate rows. It would make a difference though, if the aggregation was other than max or min.
December 3, 2013 at 12:10 pm
Note that this thread is almost 4 years old.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply