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
>
>