Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select query


select query

Author
Message
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
charipg (1/28/2010)
Please correct the below query..........

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)

If you add an alias at the end of your query to give your UNION statement a "pseudo table name", the query will return a result.
Note: Since you didn't mention whether you get an error message (or even what that error would have been) I'm just guessing that this is what you're looking for...

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





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Florian Reischl
Florian Reischl
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 3934
Your approach should work, doesn't it?

Maybe this could be a better performance:

SELECT
MAX(
CASE WHEN field1 > field2 THEN
CASE WHEN field1 > field3 THEN
field1
ELSE field3 END
ELSE
CASE WHEN field2 > field3 THEN
field2
ELSE field3 END
END
)
FROM myTable



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Florian Reischl
Florian Reischl
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 3934
Didn't notice the missing column name(s)...


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
AJHall
AJHall
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 40
Looks like someone beat me to the punch.

Aaron Hall
IT Infrastructure Consultant

Nothing is more confounding than a DBA that uses bureaucracy as a means to inflate his power. Ever try to get an index added to a government run SQL server and you'll know what I mean.
igggis
igggis
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 110
select Max(date)
from
(select date1 from table1
union all
select date2 from table1
union all
select date3 from table1 ) as tbl
sureshraghavendrarao
sureshraghavendrarao
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 357
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_1 field_2 field_3
1/1/2010 2/2/2010 3/3/2010
1/1/2011 2/2/2011 3/3/2011


Query Returns :

2011-03-03
vivekswamy85
vivekswamy85
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 70
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
The Timmer
The Timmer
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 13
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



The Timmer
The Timmer
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 13
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




yerram.vishwanatham
yerram.vishwanatham
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 14
--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.........

Vishwanath
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search