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


select query


select query

Author
Message
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
,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
fadewumi
fadewumi
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 62
what is differential backup .
fadewumi
fadewumi
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 62
why is differntial backup is so important
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

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

Cool
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)
Steve Shurts
Steve Shurts
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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
below86
below86
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 2121
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
ojrodriguez
ojrodriguez
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 35
Erroneous posting, sorry!



David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

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


ojrodriguez
ojrodriguez
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.



Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8562 Visits: 18143
Note that this thread is almost 4 years old.


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
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