Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

select query Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 1:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 6,932, Visits: 12,659
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
Post #855626
Posted Thursday, January 28, 2010 1:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #855627
Posted Thursday, January 28, 2010 1:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #855629
Posted Thursday, January 28, 2010 1:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 14, 2013 9:48 AM
Points: 474, 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.
Post #855631
Posted Tuesday, March 30, 2010 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:22 PM
Points: 45, Visits: 108
select Max(date)
from
(select date1 from table1
union all
select date2 from table1
union all
select date3 from table1 ) as tbl
Post #892954
Posted Tuesday, March 01, 2011 7:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 1:22 AM
Points: 60, Visits: 240
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


Post #1071246
Posted Saturday, September 22, 2012 9:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 01, 2013 12:12 AM
Points: 18, Visits: 38
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
Post #1363189
Posted Monday, September 24, 2012 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2013 9:33 AM
Points: 2, 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


Post #1363658
Posted Monday, September 24, 2012 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2013 9:33 AM
Points: 2, 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



Post #1363665
Posted Monday, September 24, 2012 11:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:11 AM
Points: 3, Visits: 12
--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
Post #1363796
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse