Order By in Biggest Value

select * from SaleVolumn order by Sales DESC
for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
what should i do?
****************************************
*
* This message was posted via http://www.droptable.com
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=b1aa3babb0da40f38a18f948b92aa1e1[/u
rl]
****************************************
*Is the datatype of the Sales coulmn char or varchar?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gabe Wong via droptable.com" <forum@.droptable.com> wrote in message
news:b1aa3babb0da40f38a18f948b92aa1e1@.SQ
droptable.com...
> select * from SaleVolumn order by Sales DESC
>
> for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> what should i do?
> ****************************************
*
> * This message was posted via http://www.droptable.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.droptable.com/Uwe/Abuse...a18f948b92aa1e1">
> ****************************************
*|||Looks like your Sales column could be a VARCHAR/CHAR rather than a numeric
type. If so, it will make sense to change this in your table structure but
for now you can do:
SELECT *
FROM SaleVolume
ORDER BY CAST(sales AS INTEGER) DESC
David Portas
SQL Server MVP
--sql

Order By in Biggest Value

select * from SaleVolumn order by Sales DESC
for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
what should i do?
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...18f948b92aa1e1
*****************************************
Is the datatype of the Sales coulmn char or varchar?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b1aa3babb0da40f38a18f948b92aa1e1@.SQLMonster.c om...
> select * from SaleVolumn order by Sales DESC
>
> for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> what should i do?
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse...18f948b92aa1e1
> *****************************************
|||Looks like your Sales column could be a VARCHAR/CHAR rather than a numeric
type. If so, it will make sense to change this in your table structure but
for now you can do:
SELECT *
FROM SaleVolume
ORDER BY CAST(sales AS INTEGER) DESC
David Portas
SQL Server MVP

Order By in Biggest Value

select * from SaleVolumn order by Sales DESC
for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
what should i do?
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=b1aa3babb0da40f38a18f948b92aa1e1
*****************************************Is the datatype of the Sales coulmn char or varchar?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b1aa3babb0da40f38a18f948b92aa1e1@.SQLMonster.com...
> select * from SaleVolumn order by Sales DESC
>
> for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> what should i do?
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=b1aa3babb0da40f38a18f948b92aa1e1
> *****************************************|||Looks like your Sales column could be a VARCHAR/CHAR rather than a numeric
type. If so, it will make sense to change this in your table structure but
for now you can do:
SELECT *
FROM SaleVolume
ORDER BY CAST(sales AS INTEGER) DESC
--
David Portas
SQL Server MVP
--

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 clause

I would like to order my select statement depends on column3 value,
something like this:
SELECT * from table
ORDER BY column1,column2,case when column3=1 then column4,column5 else
column5,column4 end
Any idea?
Regards,SYou're almost there:
SELECT * from table
ORDER BY
column1
, column2
, case when column3=1 then column4 else column5 end
, case when column3=1 then column5 else column4 end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"simon" <simon.zupan@.iware.si> wrote in message
news:YY2Je.1186$cE1.174161@.news.siol.net...
I would like to order my select statement depends on column3 value,
something like this:
SELECT * from table
ORDER BY column1,column2,case when column3=1 then column4,column5 else
column5,column4 end
Any idea?
Regards,S

ORDER BY before UNION syntax error

I have a list of Nationalities which I want to sort alphabetically except
for the value of Nationality which is "not disclosed" which I would like to
put at the top of the list.
I'm trying to do this with the following query but there seems to be a
problem with putting the ORDER BY in front of the UNION keyword
:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality <>'not disclosed')
ORDER BY Nationality
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality = 'not disclosed')
I tried using brackets around the first part of the query but that didn't
work.
Any help much appreciated.
PeteHi
You can add an extra columns (examples are not tested!)
SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
FROM dbo.Nationality
WHERE Nationality <>'not disclosed'
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality, 1
FROM dbo.Nationality
WHERE Nationality = 'not disclosed'
ORDER BY OrderBy, Nationality
Although you do not need a UNION in this example:
SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'not
disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality
ORDER BY OrderBy, Nationality
If you want to remove this from the result set you can use a derived table.
SELECT NationalityID, Nationality
FROM
( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
'not disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality ) A
ORDER BY OrderBy, Nationality
John
"Italian Pete" wrote:

> I have a list of Nationalities which I want to sort alphabetically except
> for the value of Nationality which is "not disclosed" which I would like t
o
> put at the top of the list.
> I'm trying to do this with the following query but there seems to be a
> problem with putting the ORDER BY in front of the UNION keyword
> :
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality <>'not disclosed')
> ORDER BY Nationality
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality = 'not disclosed')
> I tried using brackets around the first part of the query but that didn't
> work.
> Any help much appreciated.
> Pete|||Perfect!! Works a treat.
Thanks John
"John Bell" wrote:
> Hi
> You can add an extra columns (examples are not tested!)
> SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
> FROM dbo.Nationality
> WHERE Nationality <>'not disclosed'
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality, 1
> FROM dbo.Nationality
> WHERE Nationality = 'not disclosed'
> ORDER BY OrderBy, Nationality
> Although you do not need a UNION in this example:
> SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'n
ot
> disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality
> ORDER BY OrderBy, Nationality
> If you want to remove this from the result set you can use a derived table
.
> SELECT NationalityID, Nationality
> FROM
> ( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
> 'not disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality ) A
> ORDER BY OrderBy, Nationality
> John
> "Italian Pete" wrote:
>|||Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
> Perfect!! Works a treat.
> Thanks John
> "John Bell" wrote:
>
Rather than using a UNION, you can do this in a single SELECT which should
be more efficient:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
Dan|||Daniel wrote to Italian Pete on Fri, 27 May 2005 15:25:34 +0100:

> Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
>
> Rather than using a UNION, you can do this in a single SELECT which should
> be more efficient:
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
>
Just noticed that's almost the same as Italian Pete posted. However, this
gives you just the 2 columns you wanted and doesn't require a derived table.
Dan|||Daniel wrote to Daniel Crichton on Fri, 27 May 2005 15:39:22 +0100:

> Just noticed that's almost the same as Italian Pete posted. However, this
> gives you just the 2 columns you wanted and doesn't require a derived
> table.
You know what, I need more caffeine and sleep. I meant John Bell.
:\
Dan

Order By - Conditional

I wish to include a condition in my order by statement.
i.e. If the value of Field X > Y then Order by Field X,
else Order by Field Z.
I have tried inserting a select case but i cannot get it
to work
Here is an extract from the table
Order_ID Status Name
50 10 Michael
51 10 John
52 10 Peter
53 20 Adam
54 20 Bruce
The condition I would like to apply is as follows:
If Status is greater than 11, Order by Name, else order by
Order_ID
Any help wpuld be greatly appreciated
On Fri, 13 Aug 2004 05:04:53 -0700, Wes wrote:

>I wish to include a condition in my order by statement.
>i.e. If the value of Field X > Y then Order by Field X,
>else Order by Field Z.
>I have tried inserting a select case but i cannot get it
>to work
>Here is an extract from the table
>Order_ID Status Name
>50 10 Michael
>51 10 John
>52 10 Peter
>53 20 Adam
>54 20 Bruce
>The condition I would like to apply is as follows:
>If Status is greater than 11, Order by Name, else order by
>Order_ID
>Any help wpuld be greatly appreciated
Hi Wes,
Something like this?
ORDER BY Status,
CASE WHEN Status > 11 THEN Name END,
CASE WHEN Status <= 11 THEN Order_ID END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I have tried the following but with no success...
ORDER BY CASE WHEN Status > 11 THEN Name END,
CASE WHEN Status <= 11 THEN Order_ID END
Is there any other approach I can use?
Thanks in advance
[vbcol=seagreen]
>--Original Message--
>On Fri, 13 Aug 2004 05:04:53 -0700, Wes wrote:
by
>Hi Wes,
>Something like this?
>ORDER BY Status,
> CASE WHEN Status > 11 THEN Name END,
> CASE WHEN Status <= 11 THEN Order_ID END
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>
|||On Wed, 15 Sep 2004 10:14:33 -0700, Wes wrote:

>I have tried the following but with no success...
>ORDER BY CASE WHEN Status > 11 THEN Name END,
>CASE WHEN Status <= 11 THEN Order_ID END
>
>Is there any other approach I can use?
>Thanks in advance
Hi Wes,
Could you be more specific? What happened when you tried the code above?
Did you get an error? Unexpected results? Explosions in the server room?
I don't see anything wrong with the code snippet above. To goo further
into this, I must reproduce your situation on my system. To do that, you
should post more information. To be precise, I need:
1. Table structure, posted as DDL (CREATE TABLE statements; irrelevant
columns may be omitted but please do include all constraints);
2. Enough sample data to show what you want to achieve, posted as INSERT
statements;
3. The output you expect from the sample data you posted;
4. The complete SQL statement you are currently using plus the output you
are getting from it - if it's an error message, please copy and paste the
complete text;
5. A short and concise description of the business problem you're trying
to solve.
With that, I should be able to reproduce your problem and search for a
solution.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||My apologies Hugo,
the query was correct.
When building test data to send you, I noticed that the
only problemn i had was the order of the 2 case
statements. i.e. I switched the case statements
instead of ...[vbcol=seagreen]
i used ...[vbcol=seagreen]
Thanks for you help!
Wes

>--Original Message--
>On Wed, 15 Sep 2004 10:14:33 -0700, Wes wrote:
>
>Hi Wes,
>Could you be more specific? What happened when you tried
the code above?
>Did you get an error? Unexpected results? Explosions in
the server room?
>I don't see anything wrong with the code snippet above.
To goo further
>into this, I must reproduce your situation on my system.
To do that, you
>should post more information. To be precise, I need:
>1. Table structure, posted as DDL (CREATE TABLE
statements; irrelevant
>columns may be omitted but please do include all
constraints);
>2. Enough sample data to show what you want to achieve,
posted as INSERT
>statements;
>3. The output you expect from the sample data you posted;
>4. The complete SQL statement you are currently using
plus the output you
>are getting from it - if it's an error message, please
copy and paste the
>complete text;
>5. A short and concise description of the business
problem you're trying
>to solve.
>With that, I should be able to reproduce your problem and
search for a
>solution.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>