ORDER BY with SELECT DISTINCT

Hi,
I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an error
message saying that the ORDER BY needs to be included in the SELECT
statemeny.
Does anyone have a sample on how to sort a SELECT DISTINCT query ?
Niclas"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:ubdCBujQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an
> error message saying that the ORDER BY needs to be included in the SELECT
> statemeny.
> Does anyone have a sample on how to sort a SELECT DISTINCT query ?
> Niclas
>
The columns you want to order on have to be included in the SELECT list.
Here's why:
CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));
INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);
SELECT DISTINCT x FROM tbl ORDER BY z;
Result:
Server: Msg 145, Level 15, State 1, Line 9
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
Can you explain how SQL Server could return X ordered by Z in this example?
Should 100 come first or should 200 come first? There is no single answer so
that's why it has to be disallowed unless Z is in the SELECT list. Example:
SELECT DISTINCT x,z FROM tbl ORDER BY z;
The problem is with your specification rather than with SQL Server. You
haven't given us a clue about what you really want to sort on so here are a
couple of possibilities using the above example data. Notice you'll get two
different orders:
SELECT x
FROM tbl
GROUP BY x
ORDER BY MIN(z);
x
--
200
100
(2 row(s) affected)
SELECT x
FROM tbl
GROUP BY x
ORDER BY MAX(z);
x
--
100
200
(2 row(s) affected)
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
--

Order by Keyword ranking

I have the following query:
SELECT Distinct DatePosted,p.DateUpdated,Applied = Case when Email is NULL
then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
ftsolutions..position p left outer join applicantPosition a on (a.PositionID
= p.PositionID and Email = 'tscheiderich@.yahoo.com') where p.ClientID = 1234
and Contains(jobDescription,'developer and sales') order by p.DateUpdated
DESC
I want to change the option to order by keyword ranking.
How would I do this?
Do I have to use Containstable to do it or is there a way to do it directly
with this query?
Thanks,
Tom
You have to use ContainsTable - here is my stab at the query - it is hard
for me to write this as I don't really know your schema - post it for a more
complete solution.
SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL
then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
ftsolutions..position p left outer join applicantPosition a on
(a.PositionID
= p.PositionID and Email = 'tscheiderich@.yahoo.com')
join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
as ft on a.PositionID=ft.[key]
where p.ClientID = 1234
and Contains(jobDescription,'developer and sales') order by ft.[rank] desc,
p.DateUpdated DESC
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:Ouxp8tTOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I have the following query:
> SELECT Distinct DatePosted,p.DateUpdated,Applied = Case when Email is NULL
> then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
> substring(JobDescription,1,200),Posted =
> replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
> replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
> ftsolutions..position p left outer join applicantPosition a on
(a.PositionID
> = p.PositionID and Email = 'tscheiderich@.yahoo.com') where p.ClientID =
1234
> and Contains(jobDescription,'developer and sales') order by p.DateUpdated
> DESC
> I want to change the option to order by keyword ranking.
> How would I do this?
> Do I have to use Containstable to do it or is there a way to do it
directly
> with this query?
> Thanks,
> Tom
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eBriEMUOFHA.2468@.tk2msftngp13.phx.gbl...
> You have to use ContainsTable - here is my stab at the query - it is hard
> for me to write this as I don't really know your schema - post it for a
> more
> complete solution.
That was exactly what I was looking for.
I wasn't sure how to use the containstable (a little confusing). I am still
confused as to why I need the same filter (developer and sales) in both the
Containstable and Contains verbs. But that is how I always see it.
Thanks,
Tom
> SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL
> then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
> substring(JobDescription,1,200),Posted =
> replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
> replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
> ftsolutions..position p left outer join applicantPosition a on
> (a.PositionID
> = p.PositionID and Email = 'tscheiderich@.yahoo.com')
> join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
> as ft on a.PositionID=ft.[key]
> where p.ClientID = 1234
> and Contains(jobDescription,'developer and sales') order by ft.[rank]
> desc,
> p.DateUpdated DESC
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:Ouxp8tTOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> (a.PositionID
> 1234
> directly
>
|||Ouch I am sorry, you don't need the second contains.
Try this
SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL then ' '
else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location
FROM ftsolutions..position p left outer join applicantPosition a on
(a.PositionID= p.PositionID and Email = 'tscheiderich@.yahoo.com')
join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
as ft on a.PositionID=ft.[key] where p.ClientID = 1234 order by ft.[rank]
desc, p.DateUpdated DESC
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23fGemeUOFHA.1040@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eBriEMUOFHA.2468@.tk2msftngp13.phx.gbl...
> That was exactly what I was looking for.
> I wasn't sure how to use the containstable (a little confusing). I am
> still confused as to why I need the same filter (developer and sales) in
> both the Containstable and Contains verbs. But that is how I always see
> it.
> Thanks,
> Tom
>
sql

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.)
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.googlegr oups.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 result
> 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.googlegro ups.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
|||> Ok, sounds something new to me.
> As David points out, that's a fundamental definition of a table under SQL.
>
>
> 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.
|||<bangla2@.yahoo.com> wrote in message
news:1176262461.536205.289270@.o5g2000hsb.googlegro ups.com...
> 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
|||
>
> 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.

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.

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

Order By Date Format

I have inherited some code with a view that formats dates like
Mar 26, 2003
May 06, 2006
Jun 05, 2006
Example
SELECT DISTINCT TOP 5000 createDate, responsibilities
FROM vw_someview
ORDER BY createDate DESC,responsibilities
the order by doesnt come out right. The view select for the field is
convert( varchar(50), pp.createDate, 107 ) as createDate,
It is stored as 2003-03-26 14:29:41.880
Id like to order by properly but was wondering if I can do another
convert to make it order by properly?
Thanksuse order by convert(datetime,CreateDate)
take a look at these 2 example
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by CreateDate
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by convert(datetime,CreateDate)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Brian wrote:
> I have inherited some code with a view that formats dates like
> Mar 26, 2003
> May 06, 2006
> Jun 05, 2006
> Example
> SELECT DISTINCT TOP 5000 createDate, responsibilities
> FROM vw_someview
> ORDER BY createDate DESC,responsibilities
> the order by doesnt come out right. The view select for the field is
> convert( varchar(50), pp.createDate, 107 ) as createDate,
> It is stored as 2003-03-26 14:29:41.880
> Id like to order by properly but was wondering if I can do another
> convert to make it order by properly?
> Thanks|||Brian,
Sounds to me like your view needs changing, to report the date as a date.
How about leaving the formatting until you need to display it?
Currently, you're converting the date into a string and then querying that,
and then trying to sort by the result of a function on that string. That's
messy. Instead, let your view report the date itself, and tell whatever uses
that view to display the date in an appropriate format. If you really need
the view to report the date as string, then put it in a different column.
Of course, refactoring like this will be so much nicer in Visual Studio Team
Edition for Database Professionals, because you should be able to rename a
column in a view (to, say, CreateDateFormatted), and have it run through
your solution and do all the renaming for you.
Rob
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149525868.872085.191850@.i40g2000cwc.googlegroups.com...
> use order by convert(datetime,CreateDate)
> take a look at these 2 example
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by CreateDate
>
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by convert(datetime,CreateDate)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Brian wrote:
>

ORDER BY columns in SELECT list?

According to BOL, columns in an ORDER BY clause do not have to be in the SELECT
column list unless the SELECT includes DISTINCT, or the UNION operator.

Is this a SQL Server thing, or SQL standard behavior? That is, if I were to write
absolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECT
list?SQL-92 demands the columns specified in the ORDER BY clause be present in
the SELECT list. I guess the recent standards (99, 03 etc) does not have
this requirement.

--
- Anith
( Please reply to newsgroups only )

ORDER BY Clause On Bit Value Failure using SELECT DISTINCT

Hey,
I am trying to construct an ORDER BY clause for a SELECT DISTINCT query that
attempts to order the data based on a particular bit value stored in a
column named [Properties], but I get this failure message:
"ORDER BY items must appear in the select list if SELECT DISTINCT is
specified"
If I remove the "DISTINCT" keyword, then all is fine, but I do not
understand how to properly set this type of query up. Any help is very much
appreciated.
The query I am using is:
SELECT DISTINCT
ScanChangedParams.*, ScanStrategies.StrategyName AS
Expr1, ScanStrategies.TemplateID AS Expr2, ScanStrategies.ProjectID AS
Expr3,
ScanStrategies.RootTemplateID AS Expr4,
ScanStrategies.ScanError AS Expr5, TPSScanTemplates.ParamName AS Expr6
FROM ScanChangedParams WITH (NOLOCK) INNER JOIN
ScanStrategies WITH (NOLOCK) ON
ScanStrategies.ScanTaskID = ScanChangedParams.ScanTaskID AND
ScanStrategies.StrategyID =
ScanChangedParams.StrategyID INNER JOIN
TPSScanTemplates WITH (NOLOCK) ON
ScanChangedParams.ParamID = TPSScanTemplates.CommonParamID
WHERE (ScanChangedParams.ScanTaskID = 68)
ORDER BY ScanChangedParams.Properties & 1You can not use * in this specific case. Write down the list, it is a good
practice.
AMB
"AST" wrote:

> Hey,
> I am trying to construct an ORDER BY clause for a SELECT DISTINCT query th
at
> attempts to order the data based on a particular bit value stored in a
> column named [Properties], but I get this failure message:
> "ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified"
> If I remove the "DISTINCT" keyword, then all is fine, but I do not
> understand how to properly set this type of query up. Any help is very mu
ch
> appreciated.
>
> The query I am using is:
> SELECT DISTINCT
> ScanChangedParams.*, ScanStrategies.StrategyName AS
> Expr1, ScanStrategies.TemplateID AS Expr2, ScanStrategies.ProjectID AS
> Expr3,
> ScanStrategies.RootTemplateID AS Expr4,
> ScanStrategies.ScanError AS Expr5, TPSScanTemplates.ParamName AS Expr6
> FROM ScanChangedParams WITH (NOLOCK) INNER JOIN
> ScanStrategies WITH (NOLOCK) ON
> ScanStrategies.ScanTaskID = ScanChangedParams.ScanTaskID AND
> ScanStrategies.StrategyID =
> ScanChangedParams.StrategyID INNER JOIN
> TPSScanTemplates WITH (NOLOCK) ON
> ScanChangedParams.ParamID = TPSScanTemplates.CommonParamID
> WHERE (ScanChangedParams.ScanTaskID = 68)
> ORDER BY ScanChangedParams.Properties & 1
>
>|||Hey AMB,
I tried this and it still does not work. If I simply remove the DISTINCT
keyword, then all is fine.
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:694E712B-78EB-44ED-8C4F-828649A73A6F@.microsoft.com...
> You can not use * in this specific case. Write down the list, it is a good
> practice.
>
> AMB
> "AST" wrote:
>
that
much
AS|||Include "ScanChangedParams.Properties & 1" in the select list.
AMB
"AST" wrote:

> Hey AMB,
> I tried this and it still does not work. If I simply remove the DISTINCT
> keyword, then all is fine.
> Best regards,
> Bill
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:694E712B-78EB-44ED-8C4F-828649A73A6F@.microsoft.com...
> that
> much
> AS
>
>|||Hey AMB,
Thanks for the assistance!
Yes, this did result in the query executing, but it filters the rowset to
only those that satisfy the bit operation (Properties & 1). What I want to
do is sort or order by this bit value only as this particular bit pattern is
used to display textual data representation on a client window.
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:658B4D3C-28A5-4519-BC06-FD1A185E9AE0@.microsoft.com...
> Include "ScanChangedParams.Properties & 1" in the select list.
>
> AMB
> "AST" wrote:
>
DISTINCT
message
good
query
a
very
ScanStrategies.StrategyName
AS
Expr6|||I think you have to be more specific or post DDL, sample data and expected
result.
AMB
"AST" wrote:

> Hey AMB,
> Thanks for the assistance!
> Yes, this did result in the query executing, but it filters the rowset to
> only those that satisfy the bit operation (Properties & 1). What I want t
o
> do is sort or order by this bit value only as this particular bit pattern
is
> used to display textual data representation on a client window.
> Best regards,
> Bill
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:658B4D3C-28A5-4519-BC06-FD1A185E9AE0@.microsoft.com...
> DISTINCT
> message
> good
> query
> a
> very
> ScanStrategies.StrategyName
> AS
> Expr6
>
>|||<snip>
As AMD indicated, inclusion of the expression should not alter the result
set (beyond the additional column). However, it is possible that there is
something specific to your query that is not directly evident without
knowledge of the underlying DDL and data characteristics. Below is an
example that shows the expression <<shouldn not>> affect the result set.
The last query is another alternative.
set nocount on
create table #test (numval smallint, descr varchar(25))
go
insert #test (numval, descr)
select 1, 'good by'
union all
select 2, 'good by'
union all
select 3, 'goodby'
union all
select 2, 'good by'
union all
select 4, 'good by'
union all
select null, 'good by'
go
select numval, descr from #test
select distinct numval, descr from #test
select distinct numval, descr, numval & 1 as bogus from #test
order by bogus
select distinct numval, descr, numval & 1 as bogus from #test
order by numval & 1
select numval, descr from
( select distinct numval, descr from #test ) as t1
order by numval & 1|||Hey Alejandro,
After further testing, I was able to get this query to work as you described
by adding the same column again in the select query.
Thanks again!
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9D8FF8AD-3060-4C4B-8224-6A479DFA05A1@.microsoft.com...
> I think you have to be more specific or post DDL, sample data and expected
> result.
>
> AMB
> "AST" wrote:
>
to
to
pattern is
message
is a
DISTINCT
stored in
DISTINCT is
not
is
ScanStrategies.ProjectID|||Hey Scott,
Thanks for the feeback and examples. Based on AMD's and your comments and a
little more testing I have now worked this out.
Thanks again!
Best regards,
Bill
"Scott Morris" <bogus@.bogus.com> wrote in message
news:eU%23lFfJCFHA.328@.tk2msftngp13.phx.gbl...
> <snip>
> As AMD indicated, inclusion of the expression should not alter the result
> set (beyond the additional column). However, it is possible that there is
> something specific to your query that is not directly evident without
> knowledge of the underlying DDL and data characteristics. Below is an
> example that shows the expression <<shouldn not>> affect the result set.
> The last query is another alternative.
> set nocount on
> create table #test (numval smallint, descr varchar(25))
> go
> insert #test (numval, descr)
> select 1, 'good by'
> union all
> select 2, 'good by'
> union all
> select 3, 'goodby'
> union all
> select 2, 'good by'
> union all
> select 4, 'good by'
> union all
> select null, 'good by'
> go
> select numval, descr from #test
> select distinct numval, descr from #test
> select distinct numval, descr, numval & 1 as bogus from #test
> order by bogus
> select distinct numval, descr, numval & 1 as bogus from #test
> order by numval & 1
> select numval, descr from
> ( select distinct numval, descr from #test ) as t1
> order by numval & 1
>
>

ORDER BY Can be used in view?

In MSSql,"Order by" and "distinct" can be used in view?
thanks!u can use 'order by' and 'distinct' in view.u have to use 'top ' key word when use order by clause.See below example,

use pubs
go
create view vauthors
as
select distinct top 100 percent city from authors
order by city|||No. It does not make sense to do order by in the view as a view is just a partion of a table(s). Perform the ordering you required when you select from the view.

Select *
from YourView
Order By your_view_column|||I remember that the following SQL is wrong:
create view vauthors
as
select distinct top 100 percent city from authors
order by city

Now in MSSQL,it can be run?
it mustn't include the order by and distinct in view,now it can be?|||what khtan said is right. u should order by when u select from view.though it is possible that 'order by' clause can be used in view,which u asked

order by because of distinct

I have a strange situation (I think). Within a view (which explains why I use the top 100% --> to use an order by) I have this query:

select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2753
100 2753
100 2071
100 2753

Now I change the query to:

select distinct ofd_fk_off_id, ofd_fk_class_id from
(select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2071
100 2753

In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.First of all

Why are you doing a

Select Top 100 PERCENT

you want all the rows

So a simple select would do

select distinct ofd_fk_off_id, ofd_fk_class_id from
(select *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers

Second

The results are OK
in your second example your doing an ORDER BY
that's why you don't have the rows in the same order|||I use the top because the select statement is within a view and otherwise I can not use the order by.

For the results of the second query: they need to be in the same order as the first one, so first 2753 and then 2071. But because of the "select distinct" in the second query it not only performs a distinct but again an "order by" which I did not specify in my query. So my question is why it performs the "order by" and how I can prevent it.|||it's doing an order by to bring all the duplicates together
to then eliminate them|||You'll have to do a second "top 100 percent / order by"
on the Offers to get the wanted order|||Unfortunately, it's not possible to perform an extra order by, because the only correct order is using the field "ofd_sequence" and if I add it to the second query I get the results of the first one. Can't I use another statement. I tried changing the clustered index, but this didn't work either. Someone with an idea?|||OK

1- What do you have in your tables
2- What result do you want

3- We'll find something that works
Enigma in online !|||1. OK, I have three tables: tbOffers (off_id, price and some other fields) and tbOfferDetails (ofd_id, ofd_fk_off_id, ofd_fk_class_id) which are self explaining I think. Every record from the tbOfferDetails table has a foreign key to the third table tbClassifications (class_id, class_caption and some other fields) and to the table tbOffers. Order details are added and have a mandatory sequence. Within one order, a certain class_id (from tbClassifications) can occur multiple times.

2. What I want: all ofd_fk_class_id values for a certain offer (field ofd_fk_off_id in the order details table), but only one time and in the order of the sequence field "ofd_sequence" from the table tbOfferDetails.|||This must not be it but maybe it will give some ideas

Select Offers.IdOffer, ClassDetails.IdClass
From Offers
Inner Join (Select Distinct Top 100 Percent Details.IdOffer, Details.IdClassification,
From Classifications
Inner Join Details
On Details.IdClass = Classifications.IdClass
Order By Details.Id Asc) ClassDetails
On ClassDetails.IdOffer = Offers.IDOffer

???
Can you give some data examples|||Sorry, does not get the desired result. Below is some data. Maybe this will help.

tbClassifications:
class_id class_caption
2071 'Caption_2071'
2753 'Caption_2753'

tbOffers:
off_id off_date
100 '01/01/2004'

tbOfferDetails
ofd_id ofd_fk_off_id ofd_fk_class_id ofd_sequence ofd_description
1 100 2753 100 'First line'
2 100 2753 200 'Second line'
3 100 2753 300 'Third line'
4 100 2753 400 'Fourth line'
5 100 2071 500 'Fifth line'
6 100 2753 600 'Last line'|||and what result would you like ?|||what I wrote in my first post, so

ofd_fk_off_id ofd_fk_class_id
100 2753
100 2071|||Select Distinct Top 100 PERCENT Details.IdClass,Details.IdOffer,DetailsOrder.MinId
From Details
Inner Join (Select Min(Id) as MinId,Idclass
From Details
Group By IdClass) DetailsOrder
On DetailsOrder.IdClass = Details.IdClass
Order By DetailsOrder.MinId

Where
Classes = tbClassifications
Details = tbOfferDetails
Offers = tbOffers|||Finally, it works. I just changed a little bit from the last post of Karolyn. Because it's a view, a distinct was not possible so made an extra subquery of it. Anyway, thanks a lot Karolyn.

select ofd_fk_off_id, ofd_fk_class_id from
(select distinct top 100 percent tbOfferDetails.ofd_fk_class_id, tbOfferDetails.ofd_fk_off_id, minID
from tbOfferDetails inner join
(select min(ofd_sequence) as minID, ofd_fk_class_id
from tbOfferDetails
group by ofd_fk_class_id) DetailsOrder
on DetailsOrder.ofd_fk_class_id = tbOfferDetails.ofd_fk_class_id
order by DetailsOrder.minID) as Results|||Was fun to do
but as usual I'm slow on the starting blocks
thanks to you too, i've learned on limitations of views

Order By "IN"

SELECT DISTINCT * FROM Products WHERE ProductID
IN(1406,761,587,646,182)
I need to Order by the "IN"
1. 1406
2. 761
3. 587
4. 646
5. 182
Does anyone know how to do that?
From
MikeIf you use the method of a separate table, and parsing by the order the
commas are found, then you could add an identity column to the table and
order by that first.
http://www.aspfaq.com/2248
"AspMike" <mike@.pwim.com> wrote in message
news:1145898945.787474.170770@.j33g2000cwa.googlegroups.com...
> SELECT DISTINCT * FROM Products WHERE ProductID
> IN(1406,761,587,646,182)
> I need to Order by the "IN"
> 1. 1406
> 2. 761
> 3. 587
> 4. 646
> 5. 182
> Does anyone know how to do that?
> From
> Mike
>|||If this is generally a big list, a reasonable approach is to maintain a
table/ derived table of such values & use a sort column accordingly. For
shorter lists, you can either use CASE like:
ORDER BY CASE product_id WHEN 1406 THEN 1
WHEN 761 THEN 2
WHEN 587 THEN 3
WHEN 646 THEN 4
WHEN 182 THEN 5
ELSE 6
END
Or use PATINDEX or CHARINDEX string functions like:
ORDER BY CHARINDEX ( ',' + CAST( product_id AS VARCHAR ) + ',',
',1406,761,587,646,182,' )
Anith|||Unh? the IN(<list> ) is a set (actually a table value constructor)
and has no ordering by definition. Let me repeat that BY DEFINITION.
In English and with sample output, what are you trying to do?