select query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » select query

26 posts, Page 2 of 3

««1 2 3 »»

select query

Rate Topic

Display Mode

Topic Options

Author

Message

LutzM

Posted Thursday, January 28, 2010 1:46 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:04 PM
Points: 6,739, Visits: 12,167

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

Florian Reischl

Posted Thursday, January 28, 2010 1:48 PM

SSCrazy

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

Florian Reischl

Posted Thursday, January 28, 2010 1:50 PM

SSCrazy

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

Post #855629

AJHall

Posted Thursday, January 28, 2010 1:51 PM

SSC-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

igggis

Posted Tuesday, March 30, 2010 9:35 AM

SSC 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

sureshraghavendrarao

Posted Tuesday, March 01, 2011 7:38 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:05 AM
Points: 60, Visits: 227

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

vivekswamy85

Posted Saturday, September 22, 2012 9:19 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 01, 2013 12:12 AM
Points: 17, 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

The Timmer

Posted Monday, September 24, 2012 11:46 AM

Forum 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

The Timmer

Posted Monday, September 24, 2012 12:06 PM

Forum 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

yerram.vishwanatham

Posted Monday, September 24, 2012 11:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 9:06 PM
Points: 3, Visits: 6

--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 »

26 posts, Page 2 of 3

««1 2 3 »»

Permissions