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 Monday, September 24, 2012 11:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 3, 2014 6:11 AM
Points: 3, Visits: 12
--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
Post #1363797
Posted Tuesday, April 23, 2013 4:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 8:10 AM
Points: 9, Visits: 50
what is differential backup .
Post #1445718
Posted Tuesday, April 23, 2013 4:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 8:10 AM
Points: 9, Visits: 50
why is differntial backup is so important
Post #1445719
Posted Tuesday, April 23, 2013 5:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1445722
Posted Wednesday, April 24, 2013 5:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:14 PM
Points: 58, Visits: 52
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
Post #1445875
Posted Wednesday, April 24, 2013 7:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 313, Visits: 1,151
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.
Post #1445955
Posted Tuesday, December 3, 2013 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 8, 2013 9:31 AM
Points: 19, Visits: 35
Erroneous posting, sorry!


Post #1519205
Posted Tuesday, December 3, 2013 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 7,056, Visits: 7,279
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.

Post #1519325
Posted Tuesday, December 3, 2013 12:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 8, 2013 9:31 AM
Points: 19, Visits: 35
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.



Post #1519350
Posted Tuesday, December 3, 2013 12:10 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 4,043, Visits: 9,191
Note that this thread is almost 4 years old.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1519355
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse