master sysmessages: function

Showing posts with label function. Show all posts

Wednesday, March 28, 2012

Max function Problems - Stumped!

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

Max function cannot be used in an indexed Views

All,
Does anyone know a workaround for the restriction to use max in an
indexed view? seems it really can help us to improve performance, but
we MUST use max to define the view...
any suggestions?
10x
GC
GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC
No way, and usully no need to do it either. My understanding of why is
as follows:
First reason, usually MAX and MIN can be selected from a proper index
with a quick index seek - there usually is no need to further speed it
up. Second reason, it would be not that simple to re-calculte MAX and
or MIN if you delete rows with that (MAX or MIN) value. So,
implementing that would be a lot of pain in the neck for little
practical advantage.
I think just using a proper index should be good enough under most
circustances.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
sql

Max function cannot be used in an indexed Views

All,
Does anyone know a workaround for the restriction to use max in an
indexed view? seems it really can help us to improve performance, but
we MUST use max to define the view...
any suggestions?
10x
GCThere isn't. This is because indexed views are built in such a way, that
each individual transaction can be processed without having to rebuild
the entire indexed view. This is not possible if there is an aggregate
such as AVG, MIN, MAX.
You could build your own 'materialized view' and maintain it using
triggers on the base table. However, deletes or updates of the maximum
value of a group might have serious performance impact. Proper indexes
could mitigate this problem.
HTH,
Gert-Jan
GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC|||GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC
No way, and usully no need to do it either. My understanding of why is
as follows:
First reason, usually MAX and MIN can be selected from a proper index
with a quick index seek - there usually is no need to further speed it
up. Second reason, it would be not that simple to re-calculte MAX and
or MIN if you delete rows with that (MAX or MIN) value. So,
implementing that would be a lot of pain in the neck for little
practical advantage.
I think just using a proper index should be good enough under most
circustances.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||On 2 Jan 2007 05:01:25 -0800, GC wrote:
>All,
>Does anyone know a workaround for the restriction to use max in an
>indexed view? seems it really can help us to improve performance, but
>we MUST use max to define the view...
>any suggestions?
>10x
>GC
Hi GC,
I'm afraid you'll have to rol your own. You can duplicate the "under the
cover" implementation of an indexed view by creating a table to store
the results, and populating it by using INSERT INTO ... SELECT and then
the query you currently have in your view. Next, to keep it current, add
triggers on the base tables.
Simple example (untested) - note that the second update statement in the
trigger, the one that finds the new max if the old max gets deleted or
updated, can make the trigger very slow!!
CREATE TABLE dbo.TestTab
(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
CREATE VIEW dbo.TestView WITH SCHEMABINDING
AS
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE UNIQUE CLUSTERED INDEX TestIndex ON dbo.TestView(b);
go
DROP VIEW dbo.TestView;
go
CREATE TABLE dbo.TestView
(b int NOT NULL PRIMARY KEY,
MaxOfA int NOT NULL);
INSERT INTO dbo.TestView (b, MaxOfA)
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE TRIGGER TrgTestView1
ON TestTab FOR INSERT, UPDATE, DELETE
AS
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM inserted AS i
WHERE i.b = TestView.b)
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.b = TestView.b
AND i.a > TestView.MaxOfA);
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM TestTab AS t
WHERE t.b = TestView.b)
WHERE EXISTS (SELECT *
FROM deleted AS d
WHERE d.b = TestView.b
AND d.a = TestView.MaxOfA);
go
DROP TABLE TestView;
DROP TABLE TestTab;
go
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:54:05 +0100, Hugo Kornelis wrote:
(snip)
>Simple example (untested) - note that the second update statement in the
>trigger, the one that finds the new max if the old max gets deleted or
>updated, can make the trigger very slow!!
I just realized I forgot to put in code to add a row to TestView if a
new value for b is added, and code to remove a row if the last occurence
of a value is removed. Something like this:
INSERT INTO TestView (b, MaxOfA)
SELECT i.b, MAX(i.a)
FROM inserted AS i
WHERE NOT EXISTS
(SELECT *
FROM TestView AS t
WHERE t.b = i.b);
and
DELETE FROM TestView
WHERE EXISTS
(SELECT *
FROM deleted AS d
WHERE d.b = TestView.b)
AND NOT EXISTS
(SELECT *
FROM TestTab AS t
WHERE t.b = TestView.b);
The latter will, again, be slow.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Max function cannot be used in an indexed Views

All,
Does anyone know a workaround for the restriction to use max in an
indexed view? seems it really can help us to improve performance, but
we MUST use max to define the view...
any suggestions?
10x
GCThere isn't. This is because indexed views are built in such a way, that
each individual transaction can be processed without having to rebuild
the entire indexed view. This is not possible if there is an aggregate
such as AVG, MIN, MAX.
You could build your own 'materialized view' and maintain it using
triggers on the base table. However, deletes or updates of the maximum
value of a group might have serious performance impact. Proper indexes
could mitigate this problem.
HTH,
Gert-Jan
GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC|||GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC
No way, and usully no need to do it either. My understanding of why is
as follows:
First reason, usually MAX and MIN can be selected from a proper index
with a quick index seek - there usually is no need to further speed it
up. Second reason, it would be not that simple to re-calculte MAX and
or MIN if you delete rows with that (MAX or MIN) value. So,
implementing that would be a lot of pain in the neck for little
practical advantage.
I think just using a proper index should be good enough under most
circustances.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||On 2 Jan 2007 05:01:25 -0800, GC wrote:

>All,
>Does anyone know a workaround for the restriction to use max in an
>indexed view? seems it really can help us to improve performance, but
>we MUST use max to define the view...
>any suggestions?
>10x
>GC
Hi GC,
I'm afraid you'll have to rol your own. You can duplicate the "under the
cover" implementation of an indexed view by creating a table to store
the results, and populating it by using INSERT INTO ... SELECT and then
the query you currently have in your view. Next, to keep it current, add
triggers on the base tables.
Simple example (untested) - note that the second update statement in the
trigger, the one that finds the new max if the old max gets deleted or
updated, can make the trigger very slow!!
CREATE TABLE dbo.TestTab
(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
CREATE VIEW dbo.TestView WITH SCHEMABINDING
AS
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE UNIQUE CLUSTERED INDEX TestIndex ON dbo.TestView(b);
go
DROP VIEW dbo.TestView;
go
CREATE TABLE dbo.TestView
(b int NOT NULL PRIMARY KEY,
MaxOfA int NOT NULL);
INSERT INTO dbo.TestView (b, MaxOfA)
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE TRIGGER TrgTestView1
ON TestTab FOR INSERT, UPDATE, DELETE
AS
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM inserted AS i
WHERE i.b = TestView.b)
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.b = TestView.b
AND i.a > TestView.MaxOfA);
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM TestTab AS t
WHERE t.b = TestView.b)
WHERE EXISTS (SELECT *
FROM deleted AS d
WHERE d.b = TestView.b
AND d.a = TestView.MaxOfA);
go
DROP TABLE TestView;
DROP TABLE TestTab;
go
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:54:05 +0100, Hugo Kornelis wrote:
(snip)
>Simple example (untested) - note that the second update statement in the
>trigger, the one that finds the new max if the old max gets deleted or
>updated, can make the trigger very slow!!
I just realized I forgot to put in code to add a row to TestView if a
new value for b is added, and code to remove a row if the last occurence
of a value is removed. Something like this:
INSERT INTO TestView (b, MaxOfA)
SELECT i.b, MAX(i.a)
FROM inserted AS i
WHERE NOT EXISTS
(SELECT *
FROM TestView AS t
WHERE t.b = i.b);
and
DELETE FROM TestView
WHERE EXISTS
(SELECT *
FROM deleted AS d
WHERE d.b = TestView.b)
AND NOT EXISTS
(SELECT *
FROM TestTab AS t
WHERE t.b = TestView.b);
The latter will, again, be slow.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Max Function - But between a fixed value and a column

Dear Sirs / Ladies
In HQL their was a MAX Function that would compare a fixed value
( 1.95 ) with a Column.
An example is MAX( 1.95, RegularBasePrice )
If the RegularBasePrice was 0.95 it would return 1.95 or
If the RegularBasePrice was 2.49 it would return 2.49
Is their anyway to duplicate that in TSQL
Below is an example of the code under HQL
from Item;
where CurrentUnitCost > 0
and CurrentPrice > 0;
update CurrentMargin : ( ( ( CurrentPrice /
Max( 1, CurrentSplit ) ) - CurrentUnitCost ) /
Max( .01, ( CurrentPrice /
Max( 1, CurrentSplit ) ) ) ) * 100;
MarkThis is also the GREATEST( <exp list> ) function in Oracle.In Standard
SQL, you will need a CASE expression like this:
CASE WHEN x > 1.95 THEN x ELSE 1.95 END

Max function

Hi,

The question is about the way max function works in SQL. I have to implement a database design where in i have to write a stored procedure which will perform an insert in the table/tables. This table/tables will in the long run have a large number of records, i have been instructed to create another table (lets call it tableA) which will have the primary keys of all the other tables and their respective maximum values being used. While performing an insert into one of these tables, i will have to probe the tableA and find out the currently used id for that table ( in which i have to insert a new row) and use the value for insertion.

According to me, we can use a max clause in the stored procedure and achieve the following or use an Indentity column to do the same.

I will need insights into how max function works when it tells me the number of records (does it traverse thru the entire table or has the number of rows stored somewhere? I just need the number of rows in the table and nothing else but would like to know the max function working)
Also how will identity column perform under these situations.

Any information required, i will be glad to give.

VihangJust use the identity field, it's what it's designed for. If you need a table which reflects the maximum id field in each for some reason (but hopefully you don't any more), then use a view which queries the max(id) field from each table, and a 'union all' to show the lot. Something like this:

create view maxids as
select 'table1' as tablename, max(id) as maxid
from table1
union all
select 'table2' as tablename, max(id) as maxid

from table2
union all
...etc

I hope this helps...

Rob

--
Rob Farley
President - Adelaide SQL Server User Group
rob_farley@.hotmail.com (Email &

Msgr)
http://msmvps.com/blogs/robfarley|||Hi Rob,

Thank you for your prompt input. I appreciate it, I have understood what you have suggested.

I need to understand how does the max function work? does it go thru all the rows to calculate the maximum of a column?.

Regards,
Vihang|||Not when there's a useful index it can use. As these 'id' fields will be your primary keys (if not, they should be!), then the system can very easily find out what the maximum value is. Being identity columns, the system is designed to be able to very quickly report what the largest value is.|||Hi Rob,

Thanks for your insights, I now know how it really works.

Regards,
Vihang

Max function

I have some problem about sql Again!!!
I have the table name aa
id Pricecom1 Pricecom2 Pricecom3 Pricecom4
1 12 54 21 33
2 32 12 31 45
3 33 11 12 22
4 54 44 43 13
5 11 6 100 10

I want to find the maximum number of the button of every column. Form this example it should return 100

I tried
sql="select top 1 * from aaa where (select max(com1,com2,com3)as maxvalue) order by id desc"

but it doesn't workselect max(daPrice)
from (
select Pricecom1 as daPrice from aa
union
select Pricecom2 from aa
union
select Pricecom3 from aa
union
select Pricecom4 from aa
) as NormalizedTable|||It doesn't work :(


<%
sql="select max(daPrice)from (select com1 as daPrice from aaa union select com2 from aaa union select com3 from aaa union select com4 from aaa ) as NormalizedTable"
set rst=server.createobject("adodb.recordset")
rst.open sql,conn,1,3
if not rst.eof then
response.write rst("com1")&" "
response.write rst("com2")&" "
response.write rst("com3")&" "
response.write rst("NormalizedTable")&" "
end if
%>|||max can also be used as a scalar function(max(1,2,3)=3), so apply it to the result for each column:

select select max(max(com1),max(com2),max(com3)) from aaa|||Urquel , I got the message

Microsoft JET Database Engine error '80040e14'

Wrong number of arguments used with function in query expression 'max(max(com1),max(com2),max(com3))'.

<%
sql="select max(max(com1),max(com2),max(com3),max(com4)) ) as maxvalue1 from aaa "
set rst=server.createobject("adodb.recordset")
rst.open sql,conn,1,3
if not rst.eof then
response.write rst("maxvalue1")&" "
end if
%>

Note:: I use Microsoft Access 2000|||Note:: I use Microsoft Access 2000this should either have been mentioned at the outset, or you should have posted in the access forum

of course, i knew it was either access or sql server by your mention of TOP

but hey, my solution should have worked anyway

when you say "doesn't work" what do you mean -- ran but got wrong results? ran but got 0 results? didn't run, got a syntax error? what's the error?

while you're pondering how to answer, might i suggest a workaround

take the union query in the subselect, and save it as SavedQuery

then run this:select max(daPrice) from SavedQuery|||Hi r397 It didn't run. I got "The page cannot be displayed"
Now I got a litter bit idea so I tried to something like this

sql="select Max("com1")as max1 from aaa"
set rst=server.createobject("adodb.recordset")
rst.open sql,conn,1,3
sql="select Max("com2")as max2 from aaa"
set rst2=server.createobject("adodb.recordset")
rst2.open sql,conn,1,3
sql="select Max("com3")as max3 from aaa"
set rst3=server.createobject("adodb.recordset")
rst3.open sql,conn,1,3
sql="select Max("com4")as max4 from aaa"
set rst4=server.createobject("adodb.recordset")
rst4.open sql,conn,1,3

dim maxvalue
if rst("max1") >= rst("max2") and rst("max1") >= rst("max3") and rst("max1") >= rst("max4"))
then maxvalue=rst("max1")
else if rst("max2") >= rst("max1") and rst("max2") >= rst("max3") and rst("max2") >= rst("max4"))
then maxvalue=rst("max2")
else if rst("max3") >= rst("max1") and rst("max3") >= rst("max2") and rst("max3") >= rst("max4"))
then maxvalue=rst("max3")
else maxvalue=rst("max4")
end if

BUT BUT.....Don't know what to do next (still try) :p|||you may want to ask these questions in the ASP forum, because it is now likely a programming issue, not an sql problem

did you save the subquery as a saved query like i suggested?

i am certain the query of the saved query will work -- i tested it in access 97|||Ok I will try again. Sometime it is diffecult to know the problem is program or SQL. Thanks you very much.|||Sometime it is diffecult to know the problem is program or SQL.understood, but with access, which is a desktop database, you really should always test your sql in the access query window first

this is guaranteed to ensure that you eliminate sql errors before you even start programming|||OH ACCESS!!(subquerys don't work)

You will need 2 queries, the first will union the 3 columns, the second will select a max from the first query.

Query1:
select aaa.Pricecom1 as Price from aaa
union
select aaa.Pricecom2 as Price from aaa
union
select aaa.Pricecom3 as Price from aaa
union
select aaa.Pricecom4 as Price from aaa;

Qurey2:
select max(Query1.price) as MaxPrice from Query1;|||yes, urquel, subqueries do work in access, you're probably thinking of mysql

your solution is exactly the same as the one i suggested in post #6

FYI here's the slight change in syntax to make my query in post #2 work:select max(daPrice)
from [
select Pricecom1 as daPrice from aa
union
select Pricecom2 from aa
union
select Pricecom3 from aa
union
select Pricecom4 from aa
]. as NormalizedTable|||You're probably right. I just could not get a subquery with a union to work in my antiquated version of Access. :D|||how antiquated? the sql i showed in post #12 works in access 97|||Access 97. I used parenthesis instead of the sqaure brackets and got an error. Go figure!sql

MAX dont know what type is returning

Hi guys,

I'm doing some function in the SQLExpress using the max func and I cant figure out if its returning null or empty string. It always return null even it's returning index.

Please help.

Here's is the sample data of the table that I'm manipuilating

code name

code_1 name_1

code_2 name_2

tues_2 name_4

code_4 name_3

>>> What am trying to get is the maximum index of the code

Below is my function

declare @.codeIndex varchar(20)

select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2), len'code')) ,0)),0) from table where [code] like 'code%'

print @.codeIndex

if @.codeIndex is null

begin

print 'Code index is null

end

else

begin

print 'we got an index!'

end

>>> the output of this is always null even it's returning an index

Can you post a full repro? You code didn't compile (not correct number of parameters etc). Either against the pubs database or also post CREATE TABLE and some INSERT statements. I changed the code so it at least compiled (but I doubt it has correct logic), and there doesn't seem to anything wrong with the MAX function per se.: declare @.codeIndex varchar(20) select @.codeIndex = isnull(max(isnull(substring([au_lname],len('code')+2, len('code')) ,0)),0) from authors print @.codeIndex if @.codeIndex is null begin print 'Code index is null' end else begin print 'we got an index!' end -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:b4844e9a-27a2-49d0-8a11-482daa3423db@.discussions.microsoft.com...
> Hi guys, >
> I'm doing some function in the SQLExpress using the max func and I
> cant figure out if its returning null or empty string. It always return
> null even it's returning index. >
> Please help. >
> Here's is the sample data of the table that I'm manipuilating >
> code name >
> code name >
> code_1 name_1 >
> code_2 name_2 >
> tues_2 name_4 >
> code_4 name_3 > > > >>>> What am trying to get is the maximum index of the code >
> Below is my function >
> declare @.codeIndex varchar(20) >
> select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2),
> len'code')) ,0)),0) from table where [code] like 'code%' > > >
> print @.codeIndex >
> if @.codeIndex is null >
> begin >
> print 'Code index is null >
> end >
> else >
> begin >
> print 'we got an index!' >
> end > >>>> the output of this is always null even it's returning an index >
>|||

Hi NNTP,

Thanks! I already figured it out; maybe, I'm just exhausted last friday.

Wednesday, March 7, 2012

Math function

Hi All

I'm trying to find a math function (if it exists) in SQL Server. If it
doesnt exist, then maybe someone can tell me what its called so I can
do a bit more reading on it

Basically I want to do this:

Parameter Components
1 1
2 2
3 1, 2
4 4
5 1, 4
6 2, 4
7 3, 4
8 8
9 1, 8
and so on

I'd like to be able to call a function and it would return true or
false like so

functionname(1, 9) = true
so 1 is a component of 9

functionname(2, 9) = false
so 2 is not a component of 9

functionname(4, 5) = true
so 4 is a component of 5

If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,
I'd appreciate it!

Thanks in advance

SamOn 27 Aug 2004 06:00:50 -0700, Samuel Hon wrote:

> Hi All
> I'm trying to find a math function (if it exists) in SQL Server. If it
> doesnt exist, then maybe someone can tell me what its called so I can
> do a bit more reading on it
> Basically I want to do this:
> Parameter Components
> 1 1
> 2 2
> 3 1, 2
> 4 4
> 5 1, 4
> 6 2, 4
> 7 3, 4
> 8 8
> 9 1, 8
> and so on
> I'd like to be able to call a function and it would return true or
> false like so
> functionname(1, 9) = true
> so 1 is a component of 9
> functionname(2, 9) = false
> so 2 is not a component of 9
> functionname(4, 5) = true
> so 4 is a component of 5
> If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,
> I'd appreciate it!
> Thanks in advance
> Sam

Your example seems a little fuzzy, but it looks like you are saying that
the "components" of an integer n are the largest power of 2 <= n, and the
remainder when that power of 2 is subtracted from n.

If this is the case, then your function in VB.NET could be:

Public Function IsComponent(C as Integer, P as Integer) as Boolean
Dim L as integer = LargestPowerOfTwo(P)
If (C > 0) and ( (C = L) or (C = P-L) ) Then
Return True
Else
Return False
End If
End Function

Public Function LargestPowerOfTwo(X as Integer) As Integer
Dim I as Integer = 1
If X < 1 Then
Return 0
End if
Do While I*2 <= X
I = I * 2
Loop
Return I
End Function

This could easily be ported to any other language, including T-SQL should
you need it there.|||Hi Ross

Thanks for the reply

I found that the 'technique' I'm looking for is bitwise comparison.
Change the numbers to bits, and then compare

Sam

Materialized view or table function in SQL 2005

Hi,
Please advise whether SQL 2005 has the smiliar function as belows:
Materialized View in Oracle
Materialized Query Tables in DB2
Thank you.
Best Regards,
Lynn
Im not sure since I dont work with either Oracle or DB2 but I believe
indexed view would be something like that.
MC
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn
|||The purpose of this function is that the view or table is not repopulated the
data when it's queried. Usually, the data is already stored in that kind of
table when the original table is updated. When this kind of view/table is
queried, the data already exists without parsing the query to original table
and repopulated the table again. Indexed view in SQL 2005 don't have this
function.
Anyway, thanks.
Best Regards,
Lynn
"MC" wrote:

> Im not sure since I dont work with either Oracle or DB2 but I believe
> indexed view would be something like that.
>
> MC
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
>
>
|||indexed views store the aggregate result of a query (group by something
queries) and when the source table change, the view content is updated too.
like a table, you can create an index on it.
for example, if you always want to sum the sales by product, the indexed
view will contains the result of this grouping with an index on the product
column. when the source table is updated the view is updated too at the same
time so the total by product contains the new total.
when a user ask for the total of sales by product (or the sales for a group
of products or all the products) SQL server will use the indexed views
instead of scanning the big source table.
so its exactly the result you looking for.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:56067DCB-847F-4242-9B64-59463B9B79D6@.microsoft.com...[vbcol=seagreen]
> The purpose of this function is that the view or table is not repopulated
> the
> data when it's queried. Usually, the data is already stored in that kind
> of
> table when the original table is updated. When this kind of view/table is
> queried, the data already exists without parsing the query to original
> table
> and repopulated the table again. Indexed view in SQL 2005 don't have this
> function.
> Anyway, thanks.
> --
> Best Regards,
> Lynn
>
> "MC" wrote:
|||Lynn,
"On commit" materialized views in Oracle are conceptually the same as schema
bound views in SQL Server which have had a unique clustered index applied
prior to any other index. The Oracle materialized view grew out of the
snapshot functionality and has abilities like stale tolerance that do not
appear to be part of SQL Server Indexed Views.
The usage of indexed views can drastically increase the performace of select
queries at the cost insert, update, and delete. The following is a
reasonable introductory article which covers the concept:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
Luke
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn

Materialized view or table function in SQL 2005

Hi,
Please advise whether SQL 2005 has the smiliar function as belows:
Materialized View in Oracle
Materialized Query Tables in DB2
Thank you.
--
Best Regards,
LynnIm not sure since I dont work with either Oracle or DB2 but I believe
indexed view would be something like that.
MC
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn|||The purpose of this function is that the view or table is not repopulated th
e
data when it's queried. Usually, the data is already stored in that kind of
table when the original table is updated. When this kind of view/table is
queried, the data already exists without parsing the query to original table
and repopulated the table again. Indexed view in SQL 2005 don't have this
function.
Anyway, thanks.
--
Best Regards,
Lynn
"MC" wrote:

> Im not sure since I dont work with either Oracle or DB2 but I believe
> indexed view would be something like that.
>
> MC
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
>
>|||indexed views store the aggregate result of a query (group by something
queries) and when the source table change, the view content is updated too.
like a table, you can create an index on it.
for example, if you always want to sum the sales by product, the indexed
view will contains the result of this grouping with an index on the product
column. when the source table is updated the view is updated too at the same
time so the total by product contains the new total.
when a user ask for the total of sales by product (or the sales for a group
of products or all the products) SQL server will use the indexed views
instead of scanning the big source table.
so its exactly the result you looking for.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:56067DCB-847F-4242-9B64-59463B9B79D6@.microsoft.com...[vbcol=seagreen]
> The purpose of this function is that the view or table is not repopulated
> the
> data when it's queried. Usually, the data is already stored in that kind
> of
> table when the original table is updated. When this kind of view/table is
> queried, the data already exists without parsing the query to original
> table
> and repopulated the table again. Indexed view in SQL 2005 don't have this
> function.
> Anyway, thanks.
> --
> Best Regards,
> Lynn
>
> "MC" wrote:
>|||Lynn,
"On commit" materialized views in Oracle are conceptually the same as schema
bound views in SQL Server which have had a unique clustered index applied
prior to any other index. The Oracle materialized view grew out of the
snapshot functionality and has abilities like stale tolerance that do not
appear to be part of SQL Server Indexed Views.
The usage of indexed views can drastically increase the performace of select
queries at the cost insert, update, and delete. The following is a
reasonable introductory article which covers the concept:
http://www.microsoft.com/technet/pr...5/impprfiv.mspx
Luke
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn

Wednesday, March 28, 2012

Max function Problems - Stumped!

Max function cannot be used in an indexed Views

Max function cannot be used in an indexed Views

Max function cannot be used in an indexed Views

Max Function - But between a fixed value and a column

Max function

Max function

MAX dont know what type is returning

Wednesday, March 7, 2012

Math function

Materialized view or table function in SQL 2005

Materialized view or table function in SQL 2005

master sysmessages

Blog Archive

About Me