InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 21:49:55 GMT20http://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxMy 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.Wed, 24 Apr 2013 07:26:52 GMTbeloweryhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxYou 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) xWed, 24 Apr 2013 05:43:46 GMTSteve Shurtshttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspx[quote][b]fadewumi (4/23/2013)[/b][hr]why is differntial backup is so important[/quote]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.Tue, 23 Apr 2013 17:04:09 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxwhy is differntial backup is so importantTue, 23 Apr 2013 16:50:37 GMTfadewumihttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxwhat is differential backup .Tue, 23 Apr 2013 16:48:00 GMTfadewumihttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspx--sample Datadeclare @tbl table( id int ,field_1 date ,field_2 date ,field_3 date )insert into @tbl values(1,'20120823','20120824','20120825')--Queryselect max(a.mydate),a.idfrom ( select id,field_1 mydate from @tbl union all select id,field_2 from @tbl union all select id,field_3 from @tbl )agroup 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 answerMon, 24 Sep 2012 23:58:47 GMTyerram.vishwanathamhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspx--Sample Datadeclare @tbl table( id int primary key ,field_1 date ,field_2 date ,field_3 date )insert into @tbl values(1,'20120823','20120824','20120825')--Queryselect max(a.mydate),a.idfrom ( 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 )agroup 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.........[font="Comic Sans MS"]Vishwanath[/font]Mon, 24 Sep 2012 23:47:22 GMTyerram.vishwanathamhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxI 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.[code="sql"]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 unpvtgroup by akey[/code]Mon, 24 Sep 2012 12:06:46 GMTThe Timmerhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxI thought this would be an easy one but had to get some help too. Try this...[code="sql"]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 unpvtgroup by akey[/code]Mon, 24 Sep 2012 11:46:12 GMTThe Timmerhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxIt can also be done using Pivot Concept...very simpleSELECT 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 ZSat, 22 Sep 2012 21:19:37 GMTvivekswamy85http://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxselect 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 belowselect * from myTable field_1 field_2 field_31/1/2010 2/2/2010 3/3/20101/1/2011 2/2/2011 3/3/2011Query Returns :2011-03-03Tue, 01 Mar 2011 07:38:49 GMTsureshraghavendraraohttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxselect Max(date) from (select date1 from table1union allselect date2 from table1union allselect date3 from table1 ) as tblTue, 30 Mar 2010 09:35:46 GMTigggishttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxLooks like someone beat me to the punch.Thu, 28 Jan 2010 13:51:43 GMTAJHallhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxDidn't notice the missing column name(s)...Thu, 28 Jan 2010 13:50:36 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxYour approach should work, doesn't it?Maybe this could be a better performance:[code]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[/code]GreetsFloThu, 28 Jan 2010 13:48:52 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspx[quote][b]charipg (1/28/2010)[/b][hr]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)[/quote]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...[code="sql"]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) [b]uniontable [/b][/code]Thu, 28 Jan 2010 13:46:55 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxPlease 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)Thu, 28 Jan 2010 13:37:30 GMTcharipghttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspx[quote][b]charipg (1/28/2010)[/b][hr]I am not able to provide the sample data.can you pls provide the sample query?[/quote]You don't have to provide your real data. Get the basic concept and transform it into a sample table with fake column names and fake values. But make sure it represents what you're struggling with.Please remember we don't see what you see. So you have to help us help you.Thu, 28 Jan 2010 13:19:17 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspx[quote][b]charipg (1/28/2010)[/b][hr]I am not able to provide the sample data.can you pls provide the sample query?[/quote]?Thu, 28 Jan 2010 13:15:16 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxI am not able to provide the sample data.can you pls provide the sample query?Thu, 28 Jan 2010 13:14:06 GMTcharipghttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxTry MAX with an included CASE WHEN.GreetsFloThu, 28 Jan 2010 13:13:38 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspx[quote][b]charipg (1/28/2010)[/b][hr]selecting one greatest value from 3 coloumns in a table.?[/quote]Yes.How about sample data including your current query as requested before?Thu, 28 Jan 2010 13:10:19 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxselecting one greatest value from 3 coloumns in a table.?Thu, 28 Jan 2010 13:07:48 GMTcharipghttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxselecting one greatest value from 3 coloumns.?Thu, 28 Jan 2010 13:06:28 GMTcharipghttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxSounds like homework.If not, please provide sample data, expected result and what you've tried so far as described in the first link in my signature.Thu, 28 Jan 2010 13:03:44 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic855592-146-1.aspxi need a select query for below.there is 3 date colums in a table.how to find out the greatest value from these 3 columns.Thu, 28 Jan 2010 12:59:44 GMTcharipg