master sysmessages: Max function Problems

Hello all,
My Table Schema:
Id RunDate Value
----
1 06/01/06 00:00:23 2.3
2 06/01/06 00:00:25 5.6
3 06/01/06 00:02:25 5.7
4 06/01/06 00:03:25 5.8
-- and so on for a day
I need the max value for that day, along with the RunDate (which is a
datetime column).
When i use RunDate, Max(Value) , I am getting all the values in the
table for the given date range rather than the max value. If i use just
Max(Value) then i am getting the max value, but i also need teh rundate
column for further processing.
Please help.
any help is much appreciated.
Thanks!Hi
create table #t (id int not null primary key, dt datetime, v decimal(18,3))
insert into #t values (1,'06/01/06 00:00:23',2.3)
insert into #t values (2,'06/01/06 00:00:25',5.6)
insert into #t values (3,'06/01/06 00:02:25',7.1)
insert into #t values (4,'06/01/06 00:03:25',8.4)
select * from #t where v=(select max(v) from #t t1 where t1.id>=#t.id)
--or
select convert(varchar(15),dt,112) as c ,max(v)
from #t group by convert(varchar(15),dt,112)
<balapavan@.gmail.com> wrote in message
news:1150865696.766080.236230@.u72g2000cwu.googlegroups.com...
> Hello all,
> My Table Schema:
> Id RunDate Value
> ----
> 1 06/01/06 00:00:23 2.3
> 2 06/01/06 00:00:25 5.6
> 3 06/01/06 00:02:25 5.7
> 4 06/01/06 00:03:25 5.8
> -- and so on for a day
>
> I need the max value for that day, along with the RunDate (which is a
> datetime column).
> When i use RunDate, Max(Value) , I am getting all the values in the
> table for the given date range rather than the max value. If i use just
> Max(Value) then i am getting the max value, but i also need teh rundate
> column for further processing.
> Please help.
> any help is much appreciated.
> Thanks!
>|||You are partially there. You need to use some 'translation' process to
derive the date from the datetime. And then to group by that same 'date'
value.
In other words, your RunDate column should be deconstructed to a date value
'06/01/2006' -ignoring the time value. Once you GROUP BY the derived date,
the the max(Value) will be correct.
Look in Books-on-Line for 'Cast and Convert' and 'GROUP BY'.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<balapavan@.gmail.com> wrote in message
news:1150865696.766080.236230@.u72g2000cwu.googlegroups.com...
> Hello all,
> My Table Schema:
> Id RunDate Value
> ----
> 1 06/01/06 00:00:23 2.3
> 2 06/01/06 00:00:25 5.6
> 3 06/01/06 00:02:25 5.7
> 4 06/01/06 00:03:25 5.8
> -- and so on for a day
>
> I need the max value for that day, along with the RunDate (which is a
> datetime column).
> When i use RunDate, Max(Value) , I am getting all the values in the
> table for the given date range rather than the max value. If i use just
> Max(Value) then i am getting the max value, but i also need teh rundate
> column for further processing.
> Please help.
> any help is much appreciated.
> Thanks!
>|||Uri and Arnie, Thanks a lot for the help. It atleast got me started.
However, I need the time portion on the date as well, because, based on
the the date value i have to pull more columns from a different table.
I dont know if i am being clear with this.
I need the RunDate (with time portion) for which there is the
max(Value). Using this RunDate value, i would goto another table and
pull more fields. Also, the value field is not unique, for me to lookup
the table by it, after retrieving and serching reversely.
Any ideas?
Arnie Rowland wrote:
> You are partially there. You need to use some 'translation' process to
> derive the date from the datetime. And then to group by that same 'date'
> value.
> In other words, your RunDate column should be deconstructed to a date valu
e
> '06/01/2006' -ignoring the time value. Once you GROUP BY the derived date,
> the the max(Value) will be correct.
> Look in Books-on-Line for 'Cast and Convert' and 'GROUP BY'.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <balapavan@.gmail.com> wrote in message
> news:1150865696.766080.236230@.u72g2000cwu.googlegroups.com...|||Your specs are a little ambiguous. Do you mean you want the max value
for each day or for a given day you want the row who's value is the max
for the day?
If the former try something like (untested):
SELECT
DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,
MAX(Value) AS MaxValue
FROM MyTable
GROUP BY DATEADD(dd,DATEDIFF(dd,0,RunDate),0)
If the later try something like (untested):
SELECT Id, RunDate, Value
FROM MyTable AS t
INNER JOIN
(
SELECT
DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,
MAX(Value) AS MaxValue
FROM MyTable
GROUP BY DATEADD(dd,DATEDIFF(dd,0,RunDate),0)
) AS d
ON DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = d.RunDay
AND t.Value = d.MaxValue
WHERE DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = '20060621' --
Whatever day you're looking at
which, as you can see, is just the same query joined with the original
table so you can get the original row values (plus a filter so you just
get a single day, if that's what you want).
Also, you didn't say if you were using SQL 2000 or SQL 2005 (or
something else). The above should work with SQL 2000 & 2005 but in SQL
2005 there are better ways to get some of this data with the new ranking
functions. Additionally, you should include some schema in your posts
so we don't have to make so many assumptions and guesses about your
situation/environment.
*mike hodgson*
http://sqlnerd.blogspot.com
balapavan@.gmail.com wrote:

>Hello all,
>My Table Schema:
>Id RunDate Value
>----
>1 06/01/06 00:00:23 2.3
>2 06/01/06 00:00:25 5.6
>3 06/01/06 00:02:25 5.7
>4 06/01/06 00:03:25 5.8
>-- and so on for a day
>
>I need the max value for that day, along with the RunDate (which is a
>datetime column).
>When i use RunDate, Max(Value) , I am getting all the values in the
>table for the given date range rather than the max value. If i use just
>Max(Value) then i am getting the max value, but i also need teh rundate
>column for further processing.
>Please help.
>any help is much appreciated.
>Thanks!
>
>|||Mike,
That did it. Thank you so much. I went as far as coverting it to string
format, but was stuck there. Thanks for taking time in this weiry hour
to enlighten.
Thanks all!
Mike Hodgson wrote:
> Your specs are a little ambiguous. Do you mean you want the max value
> for each day or for a given day you want the row who's value is the max
> for the day?
> If the former try something like (untested):
> SELECT
> DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,
> MAX(Value) AS MaxValue
> FROM MyTable
> GROUP BY DATEADD(dd,DATEDIFF(dd,0,RunDate),0)
> If the later try something like (untested):
> SELECT Id, RunDate, Value
> FROM MyTable AS t
> INNER JOIN
> (
> SELECT
> DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,
> MAX(Value) AS MaxValue
> FROM MyTable
> GROUP BY DATEADD(dd,DATEDIFF(dd,0,RunDate),0)
> ) AS d
> ON DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = d.RunDay
> AND t.Value = d.MaxValue
> WHERE DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = '20060621' --
> Whatever day you're looking at
> which, as you can see, is just the same query joined with the original
> table so you can get the original row values (plus a filter so you just
> get a single day, if that's what you want).
> Also, you didn't say if you were using SQL 2000 or SQL 2005 (or
> something else). The above should work with SQL 2000 & 2005 but in SQL
> 2005 there are better ways to get some of this data with the new ranking
> functions. Additionally, you should include some schema in your posts
> so we don't have to make so many assumptions and guesses about your
> situation/environment.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> balapavan@.gmail.com wrote:
>
> --050002040006060001090000
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 3617
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> <title></title>
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Your specs are a little ambiguous. Do you mean you want the max
> value for each day or for a given day you want the row who's value is
> the max for the day? 
> 
> If the former try something like (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt> DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,</
tt> 
> <tt> MAX(Value) AS MaxValue</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY </tt><tt>DATEADD(dd,DATEDIFF(dd,0,RunDate),0)</tt> 
> </blockquote>
> <tt>If the later try something like (untested): 
> </tt>
> <blockquote><tt>SELECT Id, RunDate, Value 
> FROM MyTable AS t 
> INNER JOIN 
> ( 
> SELECT 
> DATEADD(dd,DATEDI
FF(dd,0,RunDate),0) AS RunDay, 
> MAX(Value) AS Max
Value 
> FROM MyTable 
> GROUP BY DATEADD(dd,DATEDIFF(dd,0,Ru
nDate),0) 
> ) AS d 
> ON DATEADD(dd,DATEDIFF(dd,0,t.RunDat
e),0) = d.RunDay 
> AND t.Value = d.MaxValue 
> WHERE DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = '20060621'
; --
> Whatever day you're looking at</tt><tt></tt> 
> </blockquote>
> <tt>which, as you can see, is just the same query joined with the
> original table so you can get the original row values (plus a filter so
> you just get a single day, if that's what you want). 
> 
> Also, you didn't say if you were using SQL 2000 or SQL 2005 (or
> something else). The above should work with SQL 2000 & 2005 but
in
> SQL 2005 there are better ways to get some of this data with the new
> ranking functions. Additionally, you should include some schema in
> your posts so we don't have to make so many assumptions and guesses
> about your situation/environment. 
> </tt><tt></tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> -- 
> mike
> hodgson 
> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a>
> 
> </div>
> 
> 
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:balapavan@.gmail.com">bala
pavan@.gmail.com</a> wrote:
> <blockquote
> cite="mid1150865696.766080.236230@.u72g2000cwu.googlegroups.com"
> type="cite">
> <pre wrap="">Hello all,
> My Table Schema:
> Id RunDate Value
> ----
> 1 06/01/06 00:00:23 2.3
> 2 06/01/06 00:00:25 5.6
> 3 06/01/06 00:02:25 5.7
> 4 06/01/06 00:03:25 5.8
> -- and so on for a day
>
> I need the max value for that day, along with the RunDate (which is a
> datetime column).
> When i use RunDate, Max(Value) , I am getting all the values in the
> table for the given date range rather than the max value. If i use just
> Max(Value) then i am getting the max value, but i also need teh rundate
> column for further processing.
> Please help.
> any help is much appreciated.
> Thanks!
> </pre>
> </blockquote>
> </body>
> </html>
> --050002040006060001090000--|||Mike,
That did it. Thank you so much. I went as far as coverting it to string
format, but was stuck there. Thanks for taking time in this weiry hour
to enlighten. BTW, I used the second solution. That gives me the whole
record, easy to do further querying on the table as well others!
Thanks all!
Mike Hodgson wrote:
> Your specs are a little ambiguous. Do you mean you want the max value
> for each day or for a given day you want the row who's value is the max
> for the day?
> If the former try something like (untested):
> SELECT
> DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,
> MAX(Value) AS MaxValue
> FROM MyTable
> GROUP BY DATEADD(dd,DATEDIFF(dd,0,RunDate),0)
> If the later try something like (untested):
> SELECT Id, RunDate, Value
> FROM MyTable AS t
> INNER JOIN
> (
> SELECT
> DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,
> MAX(Value) AS MaxValue
> FROM MyTable
> GROUP BY DATEADD(dd,DATEDIFF(dd,0,RunDate),0)
> ) AS d
> ON DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = d.RunDay
> AND t.Value = d.MaxValue
> WHERE DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = '20060621' --
> Whatever day you're looking at
> which, as you can see, is just the same query joined with the original
> table so you can get the original row values (plus a filter so you just
> get a single day, if that's what you want).
> Also, you didn't say if you were using SQL 2000 or SQL 2005 (or
> something else). The above should work with SQL 2000 & 2005 but in SQL
> 2005 there are better ways to get some of this data with the new ranking
> functions. Additionally, you should include some schema in your posts
> so we don't have to make so many assumptions and guesses about your
> situation/environment.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> balapavan@.gmail.com wrote:
>
> --050002040006060001090000
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 3617
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> <title></title>
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Your specs are a little ambiguous. Do you mean you want the max
> value for each day or for a given day you want the row who's value is
> the max for the day? 
> 
> If the former try something like (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt> DATEADD(dd,DATEDIFF(dd,0,RunDate),0) AS RunDay,</
tt> 
> <tt> MAX(Value) AS MaxValue</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY </tt><tt>DATEADD(dd,DATEDIFF(dd,0,RunDate),0)</tt> 
> </blockquote>
> <tt>If the later try something like (untested): 
> </tt>
> <blockquote><tt>SELECT Id, RunDate, Value 
> FROM MyTable AS t 
> INNER JOIN 
> ( 
> SELECT 
> DATEADD(dd,DATEDI
FF(dd,0,RunDate),0) AS RunDay, 
> MAX(Value) AS Max
Value 
> FROM MyTable 
> GROUP BY DATEADD(dd,DATEDIFF(dd,0,Ru
nDate),0) 
> ) AS d 
> ON DATEADD(dd,DATEDIFF(dd,0,t.RunDat
e),0) = d.RunDay 
> AND t.Value = d.MaxValue 
> WHERE DATEADD(dd,DATEDIFF(dd,0,t.RunDate),0) = '20060621'
; --
> Whatever day you're looking at</tt><tt></tt> 
> </blockquote>
> <tt>which, as you can see, is just the same query joined with the
> original table so you can get the original row values (plus a filter so
> you just get a single day, if that's what you want). 
> 
> Also, you didn't say if you were using SQL 2000 or SQL 2005 (or
> something else). The above should work with SQL 2000 & 2005 but
in
> SQL 2005 there are better ways to get some of this data with the new
> ranking functions. Additionally, you should include some schema in
> your posts so we don't have to make so many assumptions and guesses
> about your situation/environment. 
> </tt><tt></tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> -- 
> mike
> hodgson 
> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a>
> 
> </div>
> 
> 
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:balapavan@.gmail.com">bala
pavan@.gmail.com</a> wrote:
> <blockquote
> cite="mid1150865696.766080.236230@.u72g2000cwu.googlegroups.com"
> type="cite">
> <pre wrap="">Hello all,
> My Table Schema:
> Id RunDate Value
> ----
> 1 06/01/06 00:00:23 2.3
> 2 06/01/06 00:00:25 5.6
> 3 06/01/06 00:02:25 5.7
> 4 06/01/06 00:03:25 5.8
> -- and so on for a day
>
> I need the max value for that day, along with the RunDate (which is a
> datetime column).
> When i use RunDate, Max(Value) , I am getting all the values in the
> table for the given date range rather than the max value. If i use just
> Max(Value) then i am getting the max value, but i also need teh rundate
> column for further processing.
> Please help.
> any help is much appreciated.
> Thanks!
> </pre>
> </blockquote>
> </body>
> </html>
> --050002040006060001090000--|||The suggestion to 'translate' the datetime field is ONLY for aggregration
purposes in the query. The datetime data in the table is not changed and can
be used for what ever other processes you desire.
Note Mike's code suggestions for more ideas.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<balapavan@.gmail.com> wrote in message
news:1150867746.942889.60590@.r2g2000cwb.googlegroups.com...
> Uri and Arnie, Thanks a lot for the help. It atleast got me started.
> However, I need the time portion on the date as well, because, based on
> the the date value i have to pull more columns from a different table.
> I dont know if i am being clear with this.
> I need the RunDate (with time portion) for which there is the
> max(Value). Using this RunDate value, i would goto another table and
> pull more fields. Also, the value field is not unique, for me to lookup
> the table by it, after retrieving and serching reversely.
> Any ideas?
> Arnie Rowland wrote:
>

Wednesday, March 28, 2012

Max function Problems - Stumped!

No comments:

Post a Comment

master sysmessages

Blog Archive

About Me