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...
>
>
>
> 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 resu
lt
> in different orders depending on whether you use MIN or MAX.
So?

> 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:
>
>
>
>
>
>
>
>
> So?
>
>
> Ok, sounds something new to me.
>
> Its better be GURANTEED every time I execute this query. Thanks.
> In 2005, use the
>
> Sorry, that's not an option (because I am using server 2k).
>
>
>
>
> - 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"
>
> 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.
>
> 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:
>
> Ok, sounds something new to me.
As David points out, that's a fundamental definition of a table under SQL.
>
>
> 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
>
> Sorry, that's not an option (because I am using server 2k).
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||
>
> 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 t
he
> database where columns D, E fall into the range of your query. However, a
s
> 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 selec
t
> 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: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.htmlsql