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.