I have a case when the same query returns two different results, depending
on the generated exec plan. I managed to reproduce it on different servers
and databases.
Heres the code, the trigger should generate new number max(Broj)+1 but the
result here is 1 because join in the selec max() part returns empty result
set.
If you substitute part in the join on n.prefix = i.prefix with n.prefix =
convert(varchar(10), i.prefix) then the trigger works.
Notice that the convert function doesnt actually do anything, its there as
an example, feel free to use any method to force different exec plan.
if exists(select * from sysobjects where xtype='U' and name='tbl')
drop table tbl
go
create table tbl (
[id] int identity(1,1) not null primary key clustered,
prefix varchar(10) not null,
broj int null
)
go
insert tbl (prefix, broj) values( '2006/01-', 54)
insert tbl (prefix, broj) values( '2006/01-', 53)
insert tbl (prefix, broj) values( '2006/01-', 52)
insert tbl (prefix, broj) values( '2006/01-', 51)
insert tbl (prefix, broj) values( '2006/01-', 50)
insert tbl (prefix, broj) values( '2006/01-', 49)
insert tbl (prefix, broj) values( '2006/01-', 48)
insert tbl (prefix, broj) values( '2006/01-', 47)
insert tbl (prefix, broj) values( '2006/01-', 46)
insert tbl (prefix, broj) values( '2006/01-', 45)
insert tbl (prefix, broj) values( '2006/01-', 44)
insert tbl (prefix, broj) values( '2006/01-', 43)
insert tbl (prefix, broj) values( '2006/01-', 42)
insert tbl (prefix, broj) values( '2006/01-', 41)
insert tbl (prefix, broj) values( '2006/01-', 40)
insert tbl (prefix, broj) values( '2006/01-', 39)
insert tbl (prefix, broj) values( '2006/01-', 38)
insert tbl (prefix, broj) values( '2006/01-', 37)
insert tbl (prefix, broj) values( '2006/01-', 36)
go
if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
drop trigger tr_tbl_ins
go
create trigger tr_tbl_ins on tbl
for insert
as
update tbl
set
broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
n.prefix = i.prefix),0)+1
where
[id] = (select [id] from inserted)
go
insert into tbl (prefix)
values ('2006/01-')
select * from tbl order by broj
-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
returns NULL valueForgot to add, its for SQL Server 2005, SQL Server 2000 works as
expected...
Marko
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqJ6IuJVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
>
> if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
> go
> create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
> go
> insert tbl (prefix, broj) values( '2006/01-', 54)
> insert tbl (prefix, broj) values( '2006/01-', 53)
> insert tbl (prefix, broj) values( '2006/01-', 52)
> insert tbl (prefix, broj) values( '2006/01-', 51)
> insert tbl (prefix, broj) values( '2006/01-', 50)
> insert tbl (prefix, broj) values( '2006/01-', 49)
> insert tbl (prefix, broj) values( '2006/01-', 48)
> insert tbl (prefix, broj) values( '2006/01-', 47)
> insert tbl (prefix, broj) values( '2006/01-', 46)
> insert tbl (prefix, broj) values( '2006/01-', 45)
> insert tbl (prefix, broj) values( '2006/01-', 44)
> insert tbl (prefix, broj) values( '2006/01-', 43)
> insert tbl (prefix, broj) values( '2006/01-', 42)
> insert tbl (prefix, broj) values( '2006/01-', 41)
> insert tbl (prefix, broj) values( '2006/01-', 40)
> insert tbl (prefix, broj) values( '2006/01-', 39)
> insert tbl (prefix, broj) values( '2006/01-', 38)
> insert tbl (prefix, broj) values( '2006/01-', 37)
> insert tbl (prefix, broj) values( '2006/01-', 36)
> go
> if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
> drop trigger tr_tbl_ins
> go
> create trigger tr_tbl_ins on tbl
> for insert
> as
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
> n.prefix = i.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> go
>
> insert into tbl (prefix)
> values ('2006/01-')
> select * from tbl order by broj
> -- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
> returns NULL value
>|||It sure looks like a bug to me.
The UPDATE in the trigger can be simplified somewhat. There is no
need to include another instance of tbl in the subquery, it can be
written as a corellated subquery. This version did not have the
problem:
update tbl
set
broj = coalesce((select max(n.broj) from tbl n
where n.prefix = tbl.prefix),0)+1
where
[id] = (select [id] from inserted)
Of course you must already realize that the trigger will provide
duplicate values if more than one row is INSERTED at a time. This is
considered a vulnerability, and I think the trigger should have a test
that enforces that there is only one row in INSERTED.
Roy Harvey
Beacon Falls, CT
On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
>Heres the code, the trigger should generate new number max(Broj)+1 but the
>result here is 1 because join in the selec max() part returns empty result
>set.
>If you substitute part in the join on n.prefix = i.prefix with n.prefix =
>convert(varchar(10), i.prefix) then the trigger works.
>Notice that the convert function doesnt actually do anything, its there as
>an example, feel free to use any method to force different exec plan.
>
>if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
>go
>create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
>go
>insert tbl (prefix, broj) values( '2006/01-', 54)
>insert tbl (prefix, broj) values( '2006/01-', 53)
>insert tbl (prefix, broj) values( '2006/01-', 52)
>insert tbl (prefix, broj) values( '2006/01-', 51)
>insert tbl (prefix, broj) values( '2006/01-', 50)
>insert tbl (prefix, broj) values( '2006/01-', 49)
>insert tbl (prefix, broj) values( '2006/01-', 48)
>insert tbl (prefix, broj) values( '2006/01-', 47)
>insert tbl (prefix, broj) values( '2006/01-', 46)
>insert tbl (prefix, broj) values( '2006/01-', 45)
>insert tbl (prefix, broj) values( '2006/01-', 44)
>insert tbl (prefix, broj) values( '2006/01-', 43)
>insert tbl (prefix, broj) values( '2006/01-', 42)
>insert tbl (prefix, broj) values( '2006/01-', 41)
>insert tbl (prefix, broj) values( '2006/01-', 40)
>insert tbl (prefix, broj) values( '2006/01-', 39)
>insert tbl (prefix, broj) values( '2006/01-', 38)
>insert tbl (prefix, broj) values( '2006/01-', 37)
>insert tbl (prefix, broj) values( '2006/01-', 36)
>go
>if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
>drop trigger tr_tbl_ins
>go
>create trigger tr_tbl_ins on tbl
>for insert
>as
>update tbl
>set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
>n.prefix = i.prefix),0)+1
>where
> [id] = (select [id] from inserted)
>go
>
>insert into tbl (prefix)
>values ('2006/01-')
>select * from tbl order by broj
>-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
>returns NULL value
>|||This is not the real code, it is simplified as musch as possible to show a
point. And, yes, there are ways to fix this but thats not the point. Now,
where should I report this?
MC
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
> It sure looks like a bug to me.
> The UPDATE in the trigger can be simplified somewhat. There is no
> need to include another instance of tbl in the subquery, it can be
> written as a corellated subquery. This version did not have the
> problem:
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n
> where n.prefix = tbl.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> Of course you must already realize that the trigger will provide
> duplicate values if more than one row is INSERTED at a time. This is
> considered a vulnerability, and I think the trigger should have a test
> that enforces that there is only one row in INSERTED.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
> wrote:
>|||I could see that it was a simplified example.
The way I always did this was to open a case with Microsoft. This
cost some $$$ up front, but it was always refunded in the long run
when it proved to be a bug. Haven't had to do that in some years
though.
Roy
On Fri, 31 Mar 2006 14:21:39 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>This is not the real code, it is simplified as musch as possible to show a
>point. And, yes, there are ways to fix this but thats not the point. Now,
>where should I report this?
>
>MC|||> Now,
> where should I report this?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uiyBBvLVGHA.2156@.tk2msftngp13.phx.gb
l...
> This is not the real code, it is simplified as musch as possible to show a
> point. And, yes, there are ways to fix this but thats not the point. Now,
> where should I report this?
>
> MC
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
>|||Maybe you should report a bug:
http://lab.msdn.microsoft.com/produ...ck/Default.aspx
ML
http://milambda.blogspot.com/|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I have a case when the same query returns two different results, depending
> on the generated exec plan. I managed to reproduce it on different servers
> and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
Definitely a bug, and you should report it on
http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
exists there as well.
The bug appears to related to the virtual inserted table. I rewrote
the MAX query in a way which is more palatable to me:
select max(n.broj)
from tbl n
where exists(select * from inserted i where n.prefix = i.prefix)
However, it still returns NULL.
If I do:
SELECT * INTO #inserted FROM tbl
and then use #inserted in the trigger, I get the correct result.
Note: while the use if SELECT INTO is palatable, since you don't
have to copy the table definition in the trigger, my recommendation
is to use a table variable instead (which only should the columns
actually referenced the trigger). I did use SELECT INTO for a while,
but I found that it gave us performance problems.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've reported the bug, however I cant seem to check the status. It seems
that part of the site is down at the moment. If you want to look at it, i
created it under 'mculo'
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9797F2B151D23Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
> Definitely a bug, and you should report it on
> http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
> done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
> exists there as well.
> The bug appears to related to the virtual inserted table. I rewrote
> the MAX query in a way which is more palatable to me:
> select max(n.broj)
> from tbl n
> where exists(select * from inserted i where n.prefix = i.prefix)
> However, it still returns NULL.
> If I do:
> SELECT * INTO #inserted FROM tbl
> and then use #inserted in the trigger, I get the correct result.
> Note: while the use if SELECT INTO is palatable, since you don't
> have to copy the table definition in the trigger, my recommendation
> is to use a table variable instead (which only should the columns
> actually referenced the trigger). I did use SELECT INTO for a while,
> but I found that it gave us performance problems.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I've reported the bug, however I cant seem to check the status. It seems
> that part of the site is down at the moment. If you want to look at it, i
> created it under 'mculo'
Yeah, I also had problems with the site yesterday. I guess it will come back
later today. Keep in mind that it is still only six o'clock in the morning
in Redmond.
But I did see your bug Saturday. As you had failed to attach(*) the repro,
I augmented the bug with the information from the newsgroup.
(*) It's very easy to go wrong, due to the poor inteface on the site. First
browse the file path, and then press Add Attachment. Took me some time to
figure that out the first time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
BUG - ISNUMERIC('.') = 1
ISNUMERIC('.') returns a value of 1.
This is bad, right?
Howardhttp://www.aspfaq.com/2390
"Howard Swope" <swopehATgigamotoDOTcom> wrote in message
news:eockploLGHA.3100@.tk2msftngp13.phx.gbl...
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
> Howard
>|||Howard Swope wrote:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
>
Try this to see why isnumeric returns 1 for this string:
select cast('.' as money)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Howard Swope (swopehATgigamotoDOTcom) writes:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
Yes isnumeric() is bad. It returns 1 if the value can be converted to
any numeric data type. Which is quite a useles piece of information.
It's not a bug though.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
This is bad, right?
Howardhttp://www.aspfaq.com/2390
"Howard Swope" <swopehATgigamotoDOTcom> wrote in message
news:eockploLGHA.3100@.tk2msftngp13.phx.gbl...
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
> Howard
>|||Howard Swope wrote:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
>
Try this to see why isnumeric returns 1 for this string:
select cast('.' as money)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Howard Swope (swopehATgigamotoDOTcom) writes:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
Yes isnumeric() is bad. It returns 1 if the value can be converted to
any numeric data type. Which is quite a useles piece of information.
It's not a bug though.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Sunday, February 12, 2012
Bottom 10?
I know the select distinct TOP 10 returns top ten highest price items but
what about top 10 lowest priced items?
Thanks
S.This is a multi-part message in MIME format.
--=_NextPart_000_04E8_01C3CEEC.B41FEA20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
It's all in the ORDER BY:
select top 10
*
from
MyTable
order by
Price -- 10 cheapest
select top 10
*
from
MyTable
order by
Price DESC -- 10 most expensive
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
I know the select distinct TOP 10 returns top ten highest price items but
what about top 10 lowest priced items?
Thanks
S.
--=_NextPart_000_04E8_01C3CEEC.B41FEA20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It's all in the ORDER BY:
select top 10
=*
from
=MyTable
order by
=Price -- 10 cheapest
select top 10
=*
from
=MyTable
order by
Price DESC -- 10 most =expensive
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"SMAN" wrote in message news:ODQ$ZYxzDHA.2156=@.TK2MSFTNGP09.phx.gbl...I know the select distinct TOP 10 returns top ten highest price items =butwhat about top 10 lowest priced items?ThanksS.
--=_NextPart_000_04E8_01C3CEEC.B41FEA20--|||SELECT TOP 10 * FROM <your table> ORDER BY Price
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> I know the select distinct TOP 10 returns top ten highest price items but
> what about top 10 lowest priced items?
> Thanks
> S.
>|||ahh...makes cents to me now...thank you so much!!!!
"J Young" <thorium48@.hotmail.com> wrote in message
news:OohzYexzDHA.4060@.TK2MSFTNGP11.phx.gbl...
> SELECT TOP 10 * FROM <your table> ORDER BY Price
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> > I know the select distinct TOP 10 returns top ten highest price items
but
> > what about top 10 lowest priced items?
> >
> > Thanks
> >
> > S.
> >
> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_053D_01C3CEEF.AEBA81F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
But will it make dollars to you later ... ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:OkpBWjxzDHA.1736@.TK2MSFTNGP09.phx.gbl...
ahh...makes cents to me now...thank you so much!!!!
"J Young" <thorium48@.hotmail.com> wrote in message
news:OohzYexzDHA.4060@.TK2MSFTNGP11.phx.gbl...
> SELECT TOP 10 * FROM <your table> ORDER BY Price
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> > I know the select distinct TOP 10 returns top ten highest price items
but
> > what about top 10 lowest priced items?
> >
> > Thanks
> >
> > S.
> >
> >
>
--=_NextPart_000_053D_01C3CEEF.AEBA81F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
But will it make dollars to you later =... ;-)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"SMAN" wrote in message news:OkpBWjxzDHA.1736=@.TK2MSFTNGP09.phx.gbl...ahh...makes cents to me now...thank you so much!!!!"J Young" =wrote in messagenews:OohzYexzDHA.4060=@.TK2MSFTNGP11.phx.gbl...> SELECT TOP 10 * FROM ORDER BY =Price>> "SMAN" wrote in message> news:ODQ$ZYxzDHA.2156=@.TK2MSFTNGP09.phx.gbl...> > I know the select distinct TOP 10 returns top ten highest price itemsbut> > what about top 10 lowest priced =items?> >> > Thanks> >> > S.> =>> >>>
--=_NextPart_000_053D_01C3CEEF.AEBA81F0--|||You had better throw in 'WITH TIES'.
Else, you'll get screwed when there are a few products with the same price.
And that goes for your 'TOP 10 highest' query too.
James Hokes
"J Young" <thorium48@.hotmail.com> wrote in message
news:OohzYexzDHA.4060@.TK2MSFTNGP11.phx.gbl...
> SELECT TOP 10 * FROM <your table> ORDER BY Price
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> > I know the select distinct TOP 10 returns top ten highest price items
but
> > what about top 10 lowest priced items?
> >
> > Thanks
> >
> > S.
> >
> >
>
what about top 10 lowest priced items?
Thanks
S.This is a multi-part message in MIME format.
--=_NextPart_000_04E8_01C3CEEC.B41FEA20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
It's all in the ORDER BY:
select top 10
*
from
MyTable
order by
Price -- 10 cheapest
select top 10
*
from
MyTable
order by
Price DESC -- 10 most expensive
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
I know the select distinct TOP 10 returns top ten highest price items but
what about top 10 lowest priced items?
Thanks
S.
--=_NextPart_000_04E8_01C3CEEC.B41FEA20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It's all in the ORDER BY:
select top 10
=*
from
=MyTable
order by
=Price -- 10 cheapest
select top 10
=*
from
=MyTable
order by
Price DESC -- 10 most =expensive
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"SMAN"
--=_NextPart_000_04E8_01C3CEEC.B41FEA20--|||SELECT TOP 10 * FROM <your table> ORDER BY Price
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> I know the select distinct TOP 10 returns top ten highest price items but
> what about top 10 lowest priced items?
> Thanks
> S.
>|||ahh...makes cents to me now...thank you so much!!!!
"J Young" <thorium48@.hotmail.com> wrote in message
news:OohzYexzDHA.4060@.TK2MSFTNGP11.phx.gbl...
> SELECT TOP 10 * FROM <your table> ORDER BY Price
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> > I know the select distinct TOP 10 returns top ten highest price items
but
> > what about top 10 lowest priced items?
> >
> > Thanks
> >
> > S.
> >
> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_053D_01C3CEEF.AEBA81F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
But will it make dollars to you later ... ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:OkpBWjxzDHA.1736@.TK2MSFTNGP09.phx.gbl...
ahh...makes cents to me now...thank you so much!!!!
"J Young" <thorium48@.hotmail.com> wrote in message
news:OohzYexzDHA.4060@.TK2MSFTNGP11.phx.gbl...
> SELECT TOP 10 * FROM <your table> ORDER BY Price
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> > I know the select distinct TOP 10 returns top ten highest price items
but
> > what about top 10 lowest priced items?
> >
> > Thanks
> >
> > S.
> >
> >
>
--=_NextPart_000_053D_01C3CEEF.AEBA81F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
But will it make dollars to you later =... ;-)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"SMAN"
--=_NextPart_000_053D_01C3CEEF.AEBA81F0--|||You had better throw in 'WITH TIES'.
Else, you'll get screwed when there are a few products with the same price.
And that goes for your 'TOP 10 highest' query too.
James Hokes
"J Young" <thorium48@.hotmail.com> wrote in message
news:OohzYexzDHA.4060@.TK2MSFTNGP11.phx.gbl...
> SELECT TOP 10 * FROM <your table> ORDER BY Price
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:ODQ$ZYxzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> > I know the select distinct TOP 10 returns top ten highest price items
but
> > what about top 10 lowest priced items?
> >
> > Thanks
> >
> > S.
> >
> >
>
Subscribe to:
Posts (Atom)