Oracle DBA to learn MS SQL: items

Showing posts with label items. Show all posts

Friday, March 30, 2012

Order of cursor walk

I have table Stock which represents products in stock.
When new order came (table orders) I must reserve items in stock for that
order (table reservations).
The way I work is, that I first find all appropriate stock for my order,
walk through it and reserve the quantity until I reach
order quantity:
declare @.stockQ decimal(15,5),@.orderQ decimal(15,5),@.orderID int,@.stockID
int,@.orderIDold int,@.work bit,@.quantity decimal(15,5)
set @.orderIDold=0
declare cCur cursor local for
SELECT o.orderID,s.stockID,o.quantity,s.quantity from orders o LEFT JOIN
stock s
ON o.productID=s.productID
AND (o.quality is null OR o.quality=s.quality)
AND (o.producer is null OR o.producer=s.producer)
open cCur
fetch next from cCur into @.orderID,@.stockID,@.orderQ,@.stockQ
while @.@.fetch_status=0
begin
set @.stockQ=@.stockQ-(SELECT isnull(sum(quantity),0) from reservations
where stockID=@.stockID)
if isnull(@.stockQ,0)>0
begin
if @.orderID<>@.orderIDold
begin
SET @.work=0
SET @.orderIDold=@.orderID
set @.quantity=@.orderQ
end
if @.work=0--(if @.work=1 then all quantity for this order was
already reserved)
begin
if @.stockQ>=@.quantity
begin
INSERT INTO reservations
SELECT @.orderID,@.stockID,@.quantity
set @.work=1
end
else
begin
INSERT INTO reservations
SELECT @.orderID,@.stockID,@.stockQ
set @.quantity=@.quantity-@.stockQ
end
end
end
fetch next from cCur into @.orderID,@.stockID,@.orderQ,@.stockQ
end
close cCur
deallocate ccur
GO
It works.
The problem is, when I have in table orders more products with the same ID
and different other parameters.
With sample data you can see (just copy the script below and run my query)
that reservation that this query create is
ORDERID STOCKID quantity
---
1 1 100
Instead I reserve both orders I reserve only first.
So, I should include somehow the right order of select statement.
The reservation should be:
ORDERID STOCKID quantity
---
1 2 100
2 1 100
How can I solve that? Any idea?
This is simple example I use to explain the situation.The real example has
many parameters, not only quality and producer, and many
different products with different quantity.
So, the real combination shoul reserve the maximum possible orders.
The sample data with tables:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[stock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[stock]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[orders]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[reservations]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[reservations]
GO
CREATE TABLE [dbo].[stock] (
[stockID] [int] NOT NULL ,
[productID] [int] NOT NULL ,
[quality] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
[producer] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[orders] (
[orderID] [int] NOT NULL ,
[productID] [int] NULL ,
[quality] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[producer] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[reservations] (
[orderID] [int] NOT NULL ,
[stockID] [int] NOT NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[stock] ADD
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[stockID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[orders] ADD
CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
(
[orderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[reservations] ADD
CONSTRAINT [PK_reservations] PRIMARY KEY CLUSTERED
(
[orderID],
[stockID]
) ON [PRIMARY]
GO
INSERT INTO orders(orderID,productID,producer,quanti
ty) VALUES (1,1,'2',100)
INSERT INTO orders(orderID,productID,quality,quantit
y) VALUES (2,1,'A',100)
INSERT INTO stock(stockID,productID,quality,producer
,quantity) VALUES
(1,1,'A','2',100)
INSERT INTO stock(stockID,productID,quality,producer
,quantity) VALUES
(2,1,'B','2',100)
regards,SCursors don't have a predictable order unless you specify ORDER BY in
the cursor declaration.
You can do this possibly more efficiently without a cursor. Google for
FIFO and stock inventory in this group to find some examples.
David Portas
SQL Server MVP
--|||well, I mean the order of SELECT statement which cursor use.
I don't know how to find the right order. There is a lot of combinations.
I thought, the result of this simple example I posted should give me the
right guidline in real example.
I already look for fifo in google but haven't find the similar example.
Can you help me?
thanks ,S
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128676869.547922.139950@.g44g2000cwa.googlegroups.com...
> Cursors don't have a predictable order unless you specify ORDER BY in
> the cursor declaration.
> You can do this possibly more efficiently without a cursor. Google for
> FIFO and stock inventory in this group to find some examples.
> --
> David Portas
> SQL Server MVP
> --
>|||Take a look at this thread:
http://groups.google.co.uk/group/mi...b653f3ad3fcaa5e
David Portas
SQL Server MVP
--

Order By with Query then bottom border

Reporting Services 2000
I have a SQL query that is properly sorting a list of items I have by using
the order by clause. I created a report using the report wizard and didn't
use any fields to group by because I already have the list in the correct
order. I did use RS to keep each team on it's own page. How can I have a
border or bgcolor change when the owner of a project changes. For example
I'd like a border after Bob and before John.
Sample Data:
Team Owner Project
NY bob Remote Access
NY bob Server Reboot
NY John Network Upgrade
Here is my order by clause:
ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 ENDDid you try to use an expression to set border width?
=IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Reporting Services 2000
> I have a SQL query that is properly sorting a list of items I have by using
> the order by clause. I created a report using the report wizard and didn't
> use any fields to group by because I already have the list in the correct
> order. I did use RS to keep each team on it's own page. How can I have a
> border or bgcolor change when the owner of a project changes. For example
> I'd like a border after Bob and before John.
> Sample Data:
> Team Owner Project
> NY bob Remote Access
> NY bob Server Reboot
> NY John Network Upgrade
> Here is my order by clause:
> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>
>|||I didn't try that. Thank you.
Where can I find a list of all the functions that can be called?
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> Did you try to use an expression to set border width?
> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>|||Can I confuse this just a tad more. What if I only want a border the very
first time the owner changes and don't need a border after that?
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
>I didn't try that. Thank you.
> Where can I find a list of all the functions that can be called?
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
>> Did you try to use an expression to set border width?
>> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
>> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the
>> correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>
>|||For the function list go to SqlServer Books Online, "Using Functions in
Reporting Services" is the name of the topic.
To show the border the first time only, try this
=IIF(Fields!OwnerValue <> Previous(Fields!Owner.Value) AND
Previous(Fields!Owner.Value)= Min(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Can I confuse this just a tad more. What if I only want a border the very
> first time the owner changes and don't need a border after that?
> "Colin" <legendsfan@.spamhotmail.com> wrote in message
> news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
> >I didn't try that. Thank you.
> >
> > Where can I find a list of all the functions that can be called?
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> >> Did you try to use an expression to set border width?
> >>
> >> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> >>
> >> "Colin" wrote:
> >>
> >> Reporting Services 2000
> >> I have a SQL query that is properly sorting a list of items I have by
> >> using
> >> the order by clause. I created a report using the report wizard and
> >> didn't
> >> use any fields to group by because I already have the list in the
> >> correct
> >> order. I did use RS to keep each team on it's own page. How can I have
> >> a
> >> border or bgcolor change when the owner of a project changes. For
> >> example
> >> I'd like a border after Bob and before John.
> >>
> >> Sample Data:
> >> Team Owner Project
> >> NY bob Remote Access
> >> NY bob Server Reboot
> >> NY John Network Upgrade
> >>
> >> Here is my order by clause:
> >> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
> >>
> >>
> >>
> >>
> >
> >
>
>

Order by string...?

hey everyone...

at the moment, I have 3 status types of items in my db... approved, pending & draft...

what im wanting to know is...

can i do a ORDER BY [status is Draft] so that any items where status (VARCHAR) is draft is at the top of the list..

I can't do alphabetical order...because d is inbetween A & P...

CheersORDER
BY CASE WHEN status = 'draft'
THEN 1
ELSE 937 END
, status
, item_name

Monday, March 26, 2012

Order By items not in the select list

Books online says this about "Order By":
The ORDER BY clause can include items not appearing in the select list.
Well, that's actually strange and surprising. This is surely not new to
many of you, but I just recently saw this construction:
Use Pubs
Select Top 5 * From Titles Order by Newid()
Which, of course, picks 5 titles randomly from the Titles table. It might
not be efficient for huge tables, but it's good to know that you can do
this. Other than this, I have NEVER seen an Order By that didn't reference
a column from the Select list, and I had no idea that it was possible -- I
must have glossed over that sentence in BOL since it had no more
explanation and it wouldn't have really make sense before I knew about it
(cognitive dissonance, I suppose).
Note: If you're using Windows 2000 or later, the implementation of Newid()
returns random IDs. In earlier operating systems, I think they were
sequential.
You have to read between the lines of that Select statement to figure out
that SQL must append the expression you supply to each row of the table,
then order the result set by that expression. Not that BOL bothers to
explain this, of course.
It's kind of poor that BOL has this one sentence but doesn't go on to
explain what happens when you order by something not in the select list.
Can I order by the values taken from a field in a different table? (Not as
far as I can tell.)
Can you do this:
Select Top 5 * From Titles Order by 'Hello there'
(Yes, you can, but it's not interesting.)
BOL should say "The ORDER BY clause can include items not appearing in the
select list, and when you do that, here's what happens..."
Are there any other cases where this feature of Order By could be useful,
other than with Newid()?
David Walker"DWalker" <none@.none.com> wrote in message
news:%23UutkJ$MFHA.3512@.TK2MSFTNGP15.phx.gbl...
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not
as
> far as I can tell.)
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
You can ORDER BY any valid scalar.
Yes, you can order by a value from another table. Use a correlated
subquery:
ORDER BY
(SELECT SomeColumn
FROM SomeOtherTable
WHERE SomeOtherTable.PK = YourTable.PK)
Or, it's often very useful to ORDER BY a CASE expression... For
instance, maybe you want to return all rows where SomeColumn = 99 first,
then sort the rest by SomeColumn ascending:
ORDER BY
CASE SomeColumn
WHEN 99 THEN 1
ELSE SomeColumn
END
.. There are lots of interesting things you can do.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||To add to Adam's reply, In general, this should occur anytime you want th
e
rows in an output resultset ordered by some value (albiet a direct Column
value or calculated expression) whose value you are not specifically
interested in on the CLient, other than as a mechanism by which to sort the
rows...
I often Order by a datetime expression, which is not output in the Select
clause,
It is frequently required that an Output column based on a datetime be
formatted as a user readable string, say "Tuesday, March 14" While actually
sorting on the internal datetime value of the same column, not on the string
which is output by the SQL .
"DWalker" wrote:

> Books online says this about "Order By":
> The ORDER BY clause can include items not appearing in the select list.
> Well, that's actually strange and surprising. This is surely not new to
> many of you, but I just recently saw this construction:
> Use Pubs
> Select Top 5 * From Titles Order by Newid()
> Which, of course, picks 5 titles randomly from the Titles table. It might
> not be efficient for huge tables, but it's good to know that you can do
> this. Other than this, I have NEVER seen an Order By that didn't referenc
e
> a column from the Select list, and I had no idea that it was possible -- I
> must have glossed over that sentence in BOL since it had no more
> explanation and it wouldn't have really make sense before I knew about it
> (cognitive dissonance, I suppose).
> Note: If you're using Windows 2000 or later, the implementation of Newid()
> returns random IDs. In earlier operating systems, I think they were
> sequential.
> You have to read between the lines of that Select statement to figure out
> that SQL must append the expression you supply to each row of the table,
> then order the result set by that expression. Not that BOL bothers to
> explain this, of course.
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not a
s
> far as I can tell.)
> Can you do this:
> Select Top 5 * From Titles Order by 'Hello there'
> (Yes, you can, but it's not interesting.)
> BOL should say "The ORDER BY clause can include items not appearing in the
> select list, and when you do that, here's what happens..."
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
>
> David Walker
>|||Thanks, Adam and cbretana. That helps.
David
"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:7B887787-2D11-4886-8CE7-C007F4F93603@.microsoft.com:
> To add to Adam's reply, In general, this should occur anytime you
> want the rows in an output resultset ordered by some value (albiet a
> direct Column value or calculated expression) whose value you are not
> specifically interested in on the CLient, other than as a mechanism by
> which to sort the rows...
> I often Order by a datetime expression, which is not output in the
> Select clause,
> It is frequently required that an Output column based on a datetime be
> formatted as a user readable string, say "Tuesday, March 14" While
> actually sorting on the internal datetime value of the same column,
> not on the string which is output by the SQL .
>
> "DWalker" wrote:
>|||On Tue, 29 Mar 2005 08:08:53 -0800, DWalker wrote:

>Thanks, Adam and cbretana. That helps.
>David
Hi David,
In addition to Adam's and CBretana's reply, I'd like to point out that
the ability to order by items that are not in the select list is a T-SQL
proprietary feature. If portability of your code is important, then it's
best to include the ordering columns in the select list, so that your
query is ANSI-compliant.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> In addition to Adam's and CBretana's reply, I'd like to point out that
> the ability to order by items that are not in the select list is a T-SQL
> proprietary feature. If portability of your code is important, then it's
> best to include the ordering columns in the select list, so that your
> query is ANSI-compliant.
Maybe not ANSI-compliant, but I think "proprietary" is too harsh a term.
This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
PostgreSQL, and others all support this, in addition to SQL Server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||OK, thanks. I'm sure we won't be moving this out of SQL 2000 except
possibly to SQL 2005, but it's good to know it's not strictly ANSI-
compliant.
David
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:OBsRlIKNFHA.244@.tk2msftngp13.phx.gbl:

> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> Maybe not ANSI-compliant, but I think "proprietary" is too harsh a
> term.
> This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
> PostgreSQL, and others all support this, in addition to SQL Server.
>

order by does not work

insert table1
select distinct a,b,c
from table2
order by d,e,f
Microsoft SQL server gives error:
"Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified."
Why? Is there any option I can set to turn this feature
off?
(I have solved it by using an intermediate table. Table1 has an
identity column and that's why it is important to get the data in
right order.)<bangla2@.yahoo.com> wrote in message
news:1176182612.603459.136470@.q75g2000hsh.googlegroups.com...
> insert table1
> select distinct a,b,c
> from table2
> order by d,e,f
> Microsoft SQL server gives error:
> "Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified."
> Why? Is there any option I can set to turn this feature
> off?
The output of a SELECT DISTINCT query may have fewer rows than the base
table. So if you order by some column that isn't in the result how can SQL
Server know which row in the base table should determine the correct order?
For example you could try one of the following::
SELECT a,b,c
FROM table2
GROUP BY a,b,c
ORDER BY MIN(d), MIN(e), MIN(f);
SELECT a,b,c
FROM table2
GROUP BY a,b,c
ORDER BY MAX(d), MAX(e), MAX(f);
Both those queries will return the same rows as DISTINCT but they may result
in different orders depending on whether you use MIN or MAX.
> (I have solved it by using an intermediate table. Table1 has an
> identity column and that's why it is important to get the data in
> right order.)
>
A table has no order. IDENTITY isn't guaranteed to match insertion order and
in some circumstances the allocation order if the IDENTITY values may not
match your ORDER BY clause, especially in SQL Server 2000. In 2005, use the
ROW_NUMBER() to generate such a sequence. ROW_NUMBER() has its own
independent ORDER BY clause.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Apr 10, 12:05 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <bang...@.yahoo.com> wrote in message
> news:1176182612.603459.136470@.q75g2000hsh.googlegroups.com...
> > insert table1
> > select distinct a,b,c
> > from table2
> > order by d,e,f
> > Microsoft SQL server gives error:
> > "Msg 145, Level 15, State 1, Line 1
> > ORDER BY items must appear in the select list if SELECT DISTINCT is
> > specified."
> > Why? Is there any option I can set to turn this feature
> > off?
> The output of a SELECT DISTINCT query may have fewer rows than the base
> table. So if you order by some column that isn't in the result how can SQL
> Server know which row in the base table should determine the correct order?
> For example you could try one of the following::
> SELECT a,b,c
> FROM table2
> GROUP BY a,b,c
> ORDER BY MIN(d), MIN(e), MIN(f);
> SELECT a,b,c
> FROM table2
> GROUP BY a,b,c
> ORDER BY MAX(d), MAX(e), MAX(f);
> Both those queries will return the same rows as DISTINCT but they may result
> in different orders depending on whether you use MIN or MAX.
So?
> > (I have solved it by using an intermediate table. Table1 has an
> > identity column and that's why it is important to get the data in
> > right order.)
> A table has no order.
Ok, sounds something new to me.
>IDENTITY isn't guaranteed to match insertion order and
> in some circumstances the allocation order if the IDENTITY values may not
> match your ORDER BY clause, especially in SQL Server 2000.
Its better be GURANTEED every time I execute this query. Thanks.
In 2005, use the
> ROW_NUMBER() to generate such a sequence. ROW_NUMBER() has its own
> independent ORDER BY clause.
Sorry, that's not an option (because I am using server 2k).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||On Apr 10, 12:38 pm, bang...@.yahoo.com wrote:
> On Apr 10, 12:05 pm, "David Portas"
>
>
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> > <bang...@.yahoo.com> wrote in message
> >news:1176182612.603459.136470@.q75g2000hsh.googlegroups.com...
> > > insert table1
> > > select distinct a,b,c
> > > from table2
> > > order by d,e,f
> > > Microsoft SQL server gives error:
> > > "Msg 145, Level 15, State 1, Line 1
> > > ORDER BY items must appear in the select list if SELECT DISTINCT is
> > > specified."
> > > Why? Is there any option I can set to turn this feature
> > > off?
> > The output of a SELECT DISTINCT query may have fewer rows than the base
> > table. So if you order by some column that isn't in the result how can SQL
> > Server know which row in the base table should determine the correct order?
> > For example you could try one of the following::
> > SELECT a,b,c
> > FROM table2
> > GROUP BY a,b,c
> > ORDER BY MIN(d), MIN(e), MIN(f);
> > SELECT a,b,c
> > FROM table2
> > GROUP BY a,b,c
> > ORDER BY MAX(d), MAX(e), MAX(f);
> > Both those queries will return the same rows as DISTINCT but they may result
> > in different orders depending on whether you use MIN or MAX.
> So?
> > > (I have solved it by using an intermediate table. Table1 has an
> > > identity column and that's why it is important to get the data in
> > > right order.)
> > A table has no order.
> Ok, sounds something new to me.
> >IDENTITY isn't guaranteed to match insertion order and
> > in some circumstances the allocation order if the IDENTITY values may not
> > match your ORDER BY clause, especially in SQL Server 2000.
> Its better be GURANTEED every time I execute this query. Thanks.
> In 2005, use the
> > ROW_NUMBER() to generate such a sequence. ROW_NUMBER() has its own
> > independent ORDER BY clause.
> Sorry, that's not an option (because I am using server 2k).
>
> > --
> > David Portas, SQL Server MVP
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> > SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > -- Hide quoted text -
> - Show quoted text -- Hide quoted text -
> - Show quoted text -
select distinct a,b
from t2
order by c
What I am trying to say is FIRST select all rows order by column c.
SECOND, based on that result set, select distinct rows for column a
and b. I think this should be fairly simple to do.|||On 10 Apr, 07:38, bang...@.yahoo.com wrote:
> On Apr 10, 12:05 pm, "David Portas"
>
> > A table has no order.
> Ok, sounds something new to me.
>
In that case I highly recommend that you read some introductory books
or take a course. Unordered tables are a pretty fundamental feature of
relational databases and SQL.
> What I am trying to say is FIRST select all rows order by column c.
> SECOND, based on that result set, select distinct rows for column a
> and b. I think this should be fairly simple to do.- Hide quoted text -
>
The best way to explain what you mean is to give an example using DDL
(CREATE TABLE statements) and some sample data (INSERT statements).
Here's a guess:
CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL,
PRIMARY KEY (a,b,c));
INSERT INTO t2 (a, b, c)
SELECT 1, 2, 1 UNION ALL
SELECT 1, 2, 4 UNION ALL
SELECT 1, 3, 2 UNION ALL
SELECT 1, 3, 1 ;
What result would you want based on this sample data? Notice that the
key of t2 may be an important factor but unfortunately you didn't
specify that information. If I'm wrong then please modify my CREATE
TABLE statement to match your actual case.
As you are using SQL Server 2000 the best solution may be to use a
subquery or a join to derive a deterministic "row number" for each
row. Here's an example using the Pubs database:
SELECT au_id,
(SELECT COUNT(*)
FROM authors
WHERE au_id <= a.au_id) AS row_number
FROM authors AS a ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> > > A table has no order.
> > Ok, sounds something new to me.
> In that case I highly recommend that you read some introductory books
> or take a course. Unordered tables are a pretty fundamental feature of
> relational databases and SQL.
Gee, that was a joke for starters.
My problem is very simple (at least that's the best I can do to
without getting into the details of the actual problem).
select distinct a,b,c
from table1
order by d,e,f
First I want the server to select the rows order by d,e and f
Second, select the distinct rows from that set maintaining the
original order (so internally I would expect that the server would get
the rows from the original set in that order!! ! I know I an not find
that out without getting into the id of each row).
This should be fairly simple data manipulation to do in my opinion.|||On 10 Apr, 11:42, bang...@.yahoo.com wrote:
> My problem is very simple (at least that's the best I can do to
> without getting into the details of the actual problem).
> select distinct a,b,c
> from table1
> order by d,e,f
> First I want the server to select the rows order by d,e and f
> Second, select the distinct rows from that set maintaining the
> original order (so internally I would expect that the server would get
> the rows from the original set in that order!! ! I know I an not find
> that out without getting into the id of each row).
>
You didn't give the extra information I asked for so I can only repeat
the suggestion I made earlier. Try:
SELECT a, b, c
FROM table1
GROUP BY a, b, c
ORDER BY MIN(d), MIN(e), MIN(f);
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||<bangla2@.yahoo.com> wrote in message
news:1176187133.447900.81300@.y66g2000hsf.googlegroups.com...
> On Apr 10, 12:05 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
>> <bang...@.yahoo.com> wrote in message
>> A table has no order.
>
> Ok, sounds something new to me.
As David points out, that's a fundamental definition of a table under SQL.
>
>>IDENTITY isn't guaranteed to match insertion order and
>> in some circumstances the allocation order if the IDENTITY values may not
>> match your ORDER BY clause, especially in SQL Server 2000.
>
> Its better be GURANTEED every time I execute this query. Thanks.
Well I can't say it will be. However, there is another fundamental issue
that may arise.
Given your query, it's very possible I could be inserting 1000 rows into the
database where columns D, E fall into the range of your query. However, as
written, there would be no locking on that range, which means your select
could return some random subset of those rows.
Also, since you can only have one IDENTITY column per table (insert CELKO's
rant here), I'm not sure what you're getting at when trying to sort by 3
columns anyway.
You're much better off probably simply putting the columns into your select
and ignoring them at the other end.
>
> In 2005, use the
>> ROW_NUMBER() to generate such a sequence. ROW_NUMBER() has its own
>> independent ORDER BY clause.
>
> Sorry, that's not an option (because I am using server 2k).
>
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books
>> Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> Ok, sounds something new to me.
> As David points out, that's a fundamental definition of a table under SQL.
>
> >>IDENTITY isn't guaranteed to match insertion order and
> >> in some circumstances the allocation order if the IDENTITY values may not
> >> match your ORDER BY clause, especially in SQL Server 2000.
> > Its better be GURANTEED every time I execute this query. Thanks.
> Well I can't say it will be. However, there is another fundamental issue
> that may arise.
> Given your query, it's very possible I could be inserting 1000 rows into the
> database where columns D, E fall into the range of your query. However, as
> written, there would be no locking on that range, which means your select
> could return some random subset of those rows.
> Also, since you can only have one IDENTITY column per table (insert CELKO's
> rant here), I'm not sure what you're getting at when trying to sort by 3
> columns anyway.
> You're much better off probably simply putting the columns into your select
> and ignoring them at the other end.
>
SQL is never meant to be a set based and all that. We EXPECT and
DEMAND from SQL Server to do many other things other than just simple
set based operations. You need to think outside the box if you want to
be sql developer. Besides, SQL server very poor serving the customs
while keeping and maintaining the spirit of relational model as well.|||> >>IDENTITY isn't guaranteed to match insertion order and
> >> in some circumstances the allocation order if the IDENTITY values may not
> >> match your ORDER BY clause, especially in SQL Server 2000.
> > Its better be GURANTEED every time I execute this query. Thanks.
> Well I can't say it will be. However, there is another fundamental issue
> that may arise.
> Given your query, it's very possible I could be inserting 1000 rows into the
> database where columns D, E fall into the range of your query. However, as
> written, there would be no locking on that range, which means your select
> could return some random subset of those rows.
> Also, since you can only have one IDENTITY column per table (insert CELKO's
> rant here), I'm not sure what you're getting at when trying to sort by 3
> columns anyway.
> You're much better off probably simply putting the columns into your select
> and ignoring them at the other end.
SQL is never meant to be ONLY set based and all that. We EXPECT and
DEMAND from SQL Server to do many other things other than just simple
set based operations. You need to think outside the box if you want
to
be a sql developer. Besides, SQL server is very poor serving the
customers
while keeping and maintaining the spirit of relational model as well.
It is not possible to develop an end to end application using SQL
Server compared to other database products in the market.|||<bangla2@.yahoo.com> wrote in message
news:1176262461.536205.289270@.o5g2000hsb.googlegroups.com...
> > Ok, sounds something new to me.
>> As David points out, that's a fundamental definition of a table under
>> SQL.
>>
>> >>IDENTITY isn't guaranteed to match insertion order and
>> >> in some circumstances the allocation order if the IDENTITY values may
>> >> not
>> >> match your ORDER BY clause, especially in SQL Server 2000.
>> > Its better be GURANTEED every time I execute this query. Thanks.
>> Well I can't say it will be. However, there is another fundamental issue
>> that may arise.
>> Given your query, it's very possible I could be inserting 1000 rows into
>> the
>> database where columns D, E fall into the range of your query. However,
>> as
>> written, there would be no locking on that range, which means your select
>> could return some random subset of those rows.
>> Also, since you can only have one IDENTITY column per table (insert
>> CELKO's
>> rant here), I'm not sure what you're getting at when trying to sort by 3
>> columns anyway.
>> You're much better off probably simply putting the columns into your
>> select
>> and ignoring them at the other end.
> SQL is never meant to be a set based and all that.
Oh? Hmm, perhaps your reading of Codd and Date's work is different than
mine.
> We EXPECT and
> DEMAND from SQL Server to do many other things other than just simple
> set based operations. You need to think outside the box if you want to
> be sql developer. Besides, SQL server very poor serving the customs
> while keeping and maintaining the spirit of relational model as well.
>
Very poor serving the customs (sic. I assume you mean customers). I don't
know. Seems to me it serves them quite well and served my previous
employers very well.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||<bangla2@.yahoo.com> wrote in message
news:1176267522.771378.242170@.b75g2000hsg.googlegroups.com...
> It is not possible to develop an end to end application using SQL
> Server compared to other database products in the market.
Really? Wow. I guess the last 8 years of my life was a dream then.
Since we did exactly that.
And I'll guess a number of other people here have done exactly the same
thing.
But hey, what do we know.
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> > It is not possible to develop an end to end application using SQL
> > Server compared to other database products in the market.
> Really? Wow. I guess the last 8 years of my life was a dream then.
> Since we did exactly that.
> And I'll guess a number of other people here have done exactly the same
> thing.
> But hey, what do we know.
End to end is developing form scratch to the end product including
processes related to BEFORE and AFFTER the project using Microsoft SQl
Server ONLY!
Example: Oracle (Forms), Sybase (Powersoft)|||<bangla2@.yahoo.com> wrote in message
news:1176269834.788198.90610@.o5g2000hsb.googlegroups.com...
>> > It is not possible to develop an end to end application using SQL
>> > Server compared to other database products in the market.
>> Really? Wow. I guess the last 8 years of my life was a dream then.
>> Since we did exactly that.
>> And I'll guess a number of other people here have done exactly the same
>> thing.
>> But hey, what do we know.
> End to end is developing form scratch to the end product including
> processes related to BEFORE and AFFTER the project using Microsoft SQl
> Server ONLY!
> Example: Oracle (Forms), Sybase (Powersoft)
>
.NET integrates well with SQL Server and I never heard a customer complain
about the fact that .NET comes on a separate disc. Why should it be a
problem that there are separate SKUs for the DBMS and the developer tool?
Even in the case of the products you mentioned the API components are a
separate install as far as I recall. I'm not sure what the purpose of your
question is.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OBxaix$eHHA.3632@.TK2MSFTNGP02.phx.gbl...
> <bangla2@.yahoo.com> wrote in message
> news:1176269834.788198.90610@.o5g2000hsb.googlegroups.com...
>> > It is not possible to develop an end to end application using SQL
>> > Server compared to other database products in the market.
>> Really? Wow. I guess the last 8 years of my life was a dream then.
>> Since we did exactly that.
>> And I'll guess a number of other people here have done exactly the same
>> thing.
>> But hey, what do we know.
>> End to end is developing form scratch to the end product including
>> processes related to BEFORE and AFFTER the project using Microsoft SQl
>> Server ONLY!
>> Example: Oracle (Forms), Sybase (Powersoft)
> .NET integrates well with SQL Server and I never heard a customer complain
> about the fact that .NET comes on a separate disc. Why should it be a
> problem that there are separate SKUs for the DBMS and the developer tool?
> Even in the case of the products you mentioned the API components are a
> separate install as far as I recall. I'm not sure what the purpose of your
> question is.
Exactly, neither are part of the RDBMS itself.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 9 Apr 2007 23:42:37 -0700, bangla2@.yahoo.com wrote:
(snip)
>select distinct a,b
>from t2
>order by c
>What I am trying to say is FIRST select all rows order by column c.
>SECOND, based on that result set, select distinct rows for column a
>and b. I think this should be fairly simple to do.
Hi bangla,
Unfortunately, it is not. Consider the sample rows below (should display
properly with a fixed font). I have already executed the first step,
ordering by column c. But as you see, the duplicates for a and b are
still there.
RowNum a b c
1 1 1 1
2 2 2 2
3 1 1 3
4 3 3 4
5 1 1 5
I agree that it's fairly easy to perform the second step, removing the
duplicated for columns a and b, as well. In fact, this is so easy that I
have three different options to choose from: either I remove the rows
with rownumbers 1 and 3, or the rows wiith rownumbers 1 and 5, or the
rows with rownumbers 3 and 5. That leaves me with three different result
sets, that shoould all be considered "correct":
a b a b a b
1 1 2 2 2 2
2 2 1 1 3 3
3 3 3 3 1 1
Unfortunately, SQL is expected to return deterministic results. If three
different result sets, all with a different order, are all "correct"
then the results are not deterministic. That is why this is not allowed.
You have to tell SQL Server which of the three result sets you want. one
way of doing that, is to use the query already suggested by David:
SELECT a,b
FROM t2
GROUP BY a,b
ORDER BY MIN(c);
Which will return only this result set:
a b
1 1
2 2
3 3
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Apr 11, 10:57 am, "Greg D. Moore $Strider$"
<mooregr_deletet...@.greenms.com> wrote:
> <bang...@.yahoo.com> wrote in message
> news:1176262461.536205.289270@.o5g2000hsb.googlegroups.com...
>
>
> > > Ok, sounds something new to me.
> >> As David points out, that's a fundamental definition of a table under
> >> SQL.
> >> >>IDENTITY isn't guaranteed to match insertion order and
> >> >> in some circumstances the allocation order if the IDENTITY values may
> >> >> not
> >> >> match your ORDER BY clause, especially in SQL Server 2000.
> >> > Its better be GURANTEED every time I execute this query. Thanks.
> >> Well I can't say it will be. However, there is another fundamental issue
> >> that may arise.
> >> Given your query, it's very possible I could be inserting 1000 rows into
> >> the
> >> database where columns D, E fall into the range of your query. However,
> >> as
> >> written, there would be no locking on that range, which means your select
> >> could return some random subset of those rows.
> >> Also, since you can only have one IDENTITY column per table (insert
> >> CELKO's
> >> rant here), I'm not sure what you're getting at when trying to sort by 3
> >> columns anyway.
> >> You're much better off probably simply putting the columns into your
> >> select
> >> and ignoring them at the other end.
> > SQL is never meant to be a set based and all that.
> Oh? Hmm, perhaps your reading of Codd and Date's work is different than
> mine.
> > We EXPECT and
> > DEMAND from SQL Server to do many other things other than just simple
> > set based operations. You need to think outside the box if you want to
> > be sql developer. Besides, SQL server very poor serving the customs
> > while keeping and maintaining the spirit of relational model as well.
> Very poor serving the customs (sic. I assume you mean customers). I don't
> know. Seems to me it serves them quite well and served my previous
> employers very well.
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html- Hide quoted text -
> - Show quoted text -
You should learn to read before venturing out writing beginning sql
codes. Thanks for listening.

Friday, March 23, 2012

order by does not work

Wednesday, March 21, 2012

ORDER BY CASE

this is my query=

"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

My SortKey can be NULL. Here's the output I am getting:
(the || is to denote sortkey column)

37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|

I want use Sortkey when it is not null. desired output:

61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||Sharif Islam wrote:

Quote:

Originally Posted by

this is my query=
>
"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

ok I think figured it out, i needed a desc at the end.

SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end desc"

let me know if there's a better way to do this.

Quote:

Originally Posted by

>
My SortKey can be NULL. Here's the output I am getting:
(the || is to denote sortkey column)
>
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
>
I want use Sortkey when it is not null. desired output:
>
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||

|||Sharif Islam wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
>(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
>type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

Quote:

Originally Posted by

Quote:

Originally Posted by

>I want use Sortkey when it is not null. desired output:

Quote:

Originally Posted by

ok I think figured it out, i needed a desc at the end.

Quote:

Originally Posted by

Quote:

Originally Posted by

let me know if there's a better way to do this.

This ensures that the query will return rows with non-null SortKey
first, rows with null SortKey second.

It does /not/ ensure that the query will return rows with SortKey
"Africa" first, rows with SortKey "Brazil" second. It happened
to work that way this time, but there are no guarantees that it
will work that way every time. To get that guarantee, do this:

order by
CASE WHEN SortKey is not null then 1 else 2 end,
coalesce(SortKey,'')|||A quick lesson in good software engineering and SQL: Put the sort key
into a column in your result SELECT list and give it a name.

1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.

2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!|||>A quick lesson in good software engineering and SQL: Put the sort key

Quote:

Originally Posted by

into a column in your result SELECT list and give it a name.
>
1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.
>
2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!
>

That is a mistake class room coders often make; they don't take into
consideration the extra resource the extra columns require when say
bandwidth may be a limiting factor to scalability.

Why pass something you don't need?

This is a MICROSOFT SQL SERVER news group (you may do well to remember that)
and NOT a STANDARD SQL one - as far as I know no such group exists which
suggests there is not much call for it.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1166906010.557020.127250@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>A quick lesson in good software engineering and SQL: Put the sort key
into a column in your result SELECT list and give it a name.
>
1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.
>
2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!
>

|||Joe,

How do you answer the question:
Give me all employees of the 5 best paid managers?
This query is very easy to write with a nested TOP/ORDER BY.

SELECT * FROM EMP
WHERE MGRID IN (SELECT TOP 5 EMPID FROM MGR
ORDER BY SALARY DESC)

It gets really nasty without. Matter of fact I can't think of a way
without cheating (e.g. using ROW_NUMBER() to sneak in the ORDER BY
through the back door).

Are you saying SQL shouldn't be able to answer such queries without
escaping into the application? I doubt that's what Codd had in mind...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||>Are you saying SQL shouldn't be able to answer such queries without escaping into the application? <<

I would use the "ROW_NUMBER() OVER ()" that you guys at IBM put into
DB2 !!|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Are you saying SQL shouldn't be able to answer such queries without escaping into the application? <<

>
I would use the "ROW_NUMBER() OVER ()" that you guys at IBM put into
DB2 !!

... and the SQL Standard...

Well, that's what I referred to as cheating, since it requires usage
ROW_NUMBER() OVER ( _ORDER_BY_ ...). So you avoid one ORDER BY by
adding another. How come one is bad while the other isn't ?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||>So you avoid one ORDER BY by adding another. How come one is bad while the other isn't ? <<

Language Standards. And an underlying definition based on von Nueman's
definition of ordinal numbers in Set Theory. DUH! :)|||--CELKO-- wrote:

Quote:

Originally Posted by

Language Standards.

Winds of change are blowing and they wisper "FETCH FIRST n ROWS". Every
SQL based product has some sort of mechanism to get the job done.
Doesn't that just scream need? Whether it's TOP, ROWNUM, LIMIT OFFSET...
All of them address the need to cut pieces out of a set based on order.

Quote:

Originally Posted by

>And an underlying definition based on von Nueman's
definition of ordinal numbers in Set Theory. DUH! :)

Living in the past, are we...? The result of a refusal to acknowledge a
need is that the standard got fractured at this point.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||On Sat, 23 Dec 2006 18:17:54 -0500, Serge Rielau <srielau@.ca.ibm.com>
wrote:

Quote:

Originally Posted by

>Give me all employees of the 5 best paid managers?
>This query is very easy to write with a nested TOP/ORDER BY.
>
>SELECT * FROM EMP
WHERE MGRID IN (SELECT TOP 5 EMPID FROM MGR
ORDER BY SALARY DESC)
>
>It gets really nasty without. Matter of fact I can't think of a way
>without cheating (e.g. using ROW_NUMBER() to sneak in the ORDER BY
>through the back door).

If you allow WITH DUPS instead of arbitrarily dropping one:

SELECT *
FROM EMP
WHERE MGRID IN
(SELECT EMPID FROM MGR as M1
WHERE (SELECT count(*) from MGR as M2
WHERE M1.SALARY <= M2.SALARY) <= 5)

Roy Harvey
Beacon Falls, CT

order by and UNION

Hi, I got an error "ORDER BY items must appear in the select list if the
statement contains a UNION operator." when trying to put an order clause at
the end ot the union query.
How to set order to the whole set? Thanks.That will order the entire set, if the column exists. Does it? Can you
post the code you're having trouble with?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
at
> the end ot the union query.
> How to set order to the whole set? Thanks.
>|||When using the ORDER BY clause in a query with a UNION, you must adhere
to the ANSI SQL-92 standard, which says you cannot use expressions in
the ORDER BY clause, but only the names or ordinal positions of the
columns in the resultset.
Hope this helps,
Gert-Jan
js wrote:
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause a
t
> the end ot the union query.
> How to set order to the whole set? Thanks.|||select colA,colB
from tableA
union
select stuff as colA,blah as ColB
from tableB
order by 1,2 asc
// 1 = colA and 2=colB
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
> at the end ot the union query.
> How to set order to the whole set? Thanks.
>

Friday, March 30, 2012

Order of cursor walk

Order By with Query then bottom border

Order by string...?

Monday, March 26, 2012

Order By items not in the select list

order by does not work

order by does not work

Friday, March 23, 2012

order by does not work

Wednesday, March 21, 2012

ORDER BY CASE

order by and UNION

Oracle DBA to learn MS SQL

Blog Archive

About Me