Ordering Rows based on Values in a column  
Author Message
SaulF





PostPosted: Thu Nov 20 13:08:35 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column

Hi all,

IsProcedureModule Column will only contain a possiable of 3 values
('Blank','yes','no')
I would like to Select all rows ordered by the value 'no' first, then
'Blank', then 'yes'
can someone please help.


CREATE TABLE [ModuleList] (
[ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
[ModuleOwner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[IsProcedureModule] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_ModuleList] PRIMARY KEY CLUSTERED
(
[ModuleOwner]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO


thanks
Gerry

SQL Server142  
 
 
David





PostPosted: Thu Nov 20 13:08:35 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column > IsProcedureModule Column will only contain a possiable of 3 values

So why not add the CHECK constraint to that effect? Also why make it
VARCHAR(15)?:

CREATE TABLE ModuleList (... isproceduremodule CHAR(5) NOT NULL CHECK
(isproceduremodule IN ('Yes','No','Blank')) )

SELECT *
FROM ModuleList
ORDER BY
CASE isproceduremodule
WHEN 'No' THEN 1
WHEN 'Blank' THEN 2
WHEN 'Yes' THEN 3 END

--
David Portas
------------
Please reply only to the newsgroup
--


 
 
Tom





PostPosted: Thu Nov 20 13:07:29 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column This is a multi-part message in MIME format.

------=_NextPart_000_018F_01C3AF6F.A2430E00
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit

Try:

select
*
from
MyTable
order by
case IsProcedureModule
when 'no' then 1
when 'Blank' then 2
when 'yes' then 3
end

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql





Hi all,

IsProcedureModule Column will only contain a possiable of 3 values
('Blank','yes','no')
I would like to Select all rows ordered by the value 'no' first, then
'Blank', then 'yes'
can someone please help.


CREATE TABLE [ModuleList] (
[ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
[ModuleOwner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[IsProcedureModule] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_ModuleList] PRIMARY KEY CLUSTERED
(
[ModuleOwner]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO


thanks
Gerry


------=_NextPart_000_018F_01C3AF6F.A2430E00
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>Try:</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
*</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>from</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
MyTable</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>order by</FONT></DIV>
<DIV><FONT size=3D2><FONT face=3D"Courier New">&nbsp;&nbsp;&nbsp; case =
<FONT=20
size=3D3>IsProcedureModule</FONT></FONT></FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp; when=20
'no'&nbsp;&nbsp;&nbsp; then 1</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp; when=20
'Blank' then 2</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp; when=20
'yes'&nbsp;&nbsp; then 3</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
end</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT><BR>-- <BR>Tom</DIV>
<DIV>&nbsp;</DIV>
<DIV>---------------------------------------------------------------<BR>T=
homas=20
A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =
Server=20
Professional<BR>Toronto, ON Canada<BR><A=20
href=3D"http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>"Gerry Viator" &lt;<A=20

message <A=20

@TK2MSFTNGP11.phx.gbl</A>...</DIV><BR>Hi=20
all,<BR><BR>IsProcedureModule Column will only contain a possiable of 3=20
values<BR>('Blank','yes','no')<BR>I would like to Select all rows =
ordered by the=20
value 'no' first, then<BR>'Blank', then 'yes'<BR>can someone please=20
help.<BR><BR><BR>CREATE TABLE [ModuleList] (<BR>&nbsp;[ModuleID] [int] =
IDENTITY=20
(1, 1) NOT NULL ,<BR>&nbsp;[ModuleOwner] [varchar] (100) COLLATE=20
SQL_Latin1_General_CP1_CI_AS NOT NULL<BR>,<BR>&nbsp;[IsProcedureModule]=20
[varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT<BR>NULL=20
,<BR>&nbsp;CONSTRAINT [PK_ModuleList] PRIMARY KEY&nbsp;=20
CLUSTERED<BR>&nbsp;(<BR>&nbsp; [ModuleOwner]<BR>&nbsp;) WITH&nbsp; =
FILLFACTOR =3D=20
90&nbsp; ON [PRIMARY]<BR>) ON=20
[PRIMARY]<BR>GO<BR><BR><BR>thanks<BR>Gerry<BR><BR></BODY></HTML>

------=_NextPart_000_018F_01C3AF6F.A2430E00--

 
 
Trey





PostPosted: Thu Nov 20 13:11:31 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column order by
case IsProcedureModule
when 'no' then 1
when 'Blank' then 2
else 3 end



>
> Hi all,
>
> IsProcedureModule Column will only contain a possiable of 3 values
> ('Blank','yes','no')
> I would like to Select all rows ordered by the value 'no' first, then
> 'Blank', then 'yes'
> can someone please help.
>
>
> CREATE TABLE [ModuleList] (
> [ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ModuleOwner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [IsProcedureModule] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> CONSTRAINT [PK_ModuleList] PRIMARY KEY CLUSTERED
> (
> [ModuleOwner]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
> thanks
> Gerry
>
>


 
 
sampangi





PostPosted: Thu Nov 20 13:18:33 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Create a Lookup table called IsProcedureModuleType with
columns
IsProcedureModuleTypeId,IsProcedureModuleType
and with following data

IsProcedureModuleTypeId IsProcedureModuleType
1 'no'
2 'Blank'
3 'yes'

Your ModuleList table will have the columns
ModuleID,ModuleOwner,IsProcedureModuleTypeId

So all you need to do is join table ModuleList with
IsProcedureModuleType table and then order by
IsProcedureModuleTypeId

HTH,
Srinivas Sampangi
>-----Original Message-----
>
>Hi all,
>
>IsProcedureModule Column will only contain a possiable of
3 values
>('Blank','yes','no')
>I would like to Select all rows ordered by the value 'no'
first, then
>'Blank', then 'yes'
>can someone please help.
>
>
>CREATE TABLE [ModuleList] (
> [ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ModuleOwner] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>,
> [IsProcedureModule] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> CONSTRAINT [PK_ModuleList] PRIMARY KEY CLUSTERED
> (
> [ModuleOwner]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>
>thanks
>Gerry
>
>
>.
>
 
 
Gerry





PostPosted: Thu Nov 20 13:32:24 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Thanks everyone for your help.

Gerry



 
 
Delbert





PostPosted: Thu Nov 20 14:23:58 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column
(continuing the "So why not" theme )

So why not go ahead and create computed column(s):

IsProcedureModule_order_No_Blank_Yes AS
CASE isproceduremodule
WHEN 'No' THEN 1
WHEN 'Blank' THEN 2
WHEN 'Yes' THEN 3
END,
IsProcedureModule_order_Yes_Blank_No AS
CASE isproceduremodule
WHEN 'Yes' THEN 1
WHEN 'Blank' THEN 2
WHEN 'No' THEN 3
END

thus reaping a variety of benefits.

select *
from ModuleList
order by IsProcedureModule_order_No_Blank_Yes

select *
from ModuleList
order by IsProcedureModule_order_Yes_Blank_No

Bye,
Delbert Glass


 
 
Steve





PostPosted: Thu Nov 20 14:53:35 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column One more suggestion:

order by charindex(IsProcedureModule+';','no;Blank;yes;')

SK



>Hi all,
>
>IsProcedureModule Column will only contain a possiable of 3 values
>('Blank','yes','no')
>I would like to Select all rows ordered by the value 'no' first, then
>'Blank', then 'yes'
>can someone please help.
>
>
>CREATE TABLE [ModuleList] (
> [ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ModuleOwner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>,
> [IsProcedureModule] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> CONSTRAINT [PK_ModuleList] PRIMARY KEY CLUSTERED
> (
> [ModuleOwner]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>
>thanks
>Gerry
>
>
>
>

 
 
Alejandro





PostPosted: Thu Nov 20 15:20:27 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Just for fun,

...
order by len(IsProcedureModule) % 2, ascii(IsProcedureModule)



AMB


 
 
Alejandro





PostPosted: Thu Nov 20 15:22:41 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Or,

...
order by substring(IsProcedureModule, 2, 1) desc



AMB


 
 
Alejandro





PostPosted: Thu Nov 20 15:43:38 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column This is a multi-part message in MIME format.

------=_NextPart_000_006F_01C3AF85.72DD4200
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Or,

...
order by
substring(IsProcedureModule, 3, 1)



AMB
------=_NextPart_000_006F_01C3AF85.72DD4200
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3D"Courier New" size=3D2>Or,</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>...</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>order by</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp;=20
substring(IsProcedureModule, 3, 1)</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>AMB</FONT></DIV></BODY></HTML>

------=_NextPart_000_006F_01C3AF85.72DD4200--

 
 
Delbert





PostPosted: Thu Nov 20 16:31:17 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column For more fun:

ORDER BY (LEN(IsProcedureModule)-2)*2%5

ORDER BY LEN(IsProcedureModule)*LEN(IsProcedureModule)%10

ORDER BY LEN(IsProcedureModule)*(LEN(IsProcedureModule)%4)

ORDER BY 2*LEN(IsProcedureModule)%4-2*LEN(IsProcedureModule)%3

Bye,
Delbert Glass


 
 
Steve





PostPosted: Thu Nov 20 16:48:16 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column order by 7&-ascii(IsProcedureModule)

order by sin(ascii(IsProcedureModule))

order by difference(IsProcedureModule,'yenlk')

SK



> Or,
>
> ...
> order by
> substring(IsProcedureModule, 3, 1)
>
>
>
> AMB

 
 
Delbert





PostPosted: Thu Nov 20 16:50:24 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column So why not include a surprise for latter? ;-)

ORDER BY binary_checksum(upper(IsProcedureModule))%1000

Bye,
Delbert Glass


 
 
Steve





PostPosted: Thu Nov 20 16:54:11 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column order by 6|len(IsProcedureModule)





> order by 7&-ascii(IsProcedureModule)
>
> order by sin(ascii(IsProcedureModule))
>
> order by difference(IsProcedureModule,'yenlk')
>
> SK
>

>
>> Or,
>>
>> ...
>> order by
>> substring(IsProcedureModule, 3, 1)
>>
>>
>>
>> AMB
>
>

 
 
Steve





PostPosted: Thu Nov 20 17:03:30 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Oops - didn't watch for duplicates. Should be

2^-~len(IsProcedureModule)

SK



> order by 6|len(IsProcedureModule)
>
>
>

>
>> order by 7&-ascii(IsProcedureModule)
>>
>> order by sin(ascii(IsProcedureModule))
>>
>> order by difference(IsProcedureModule,'yenlk')
>>
>> SK
>>

>>
>>> Or,
>>>
>>> ...
>>> order by
>>> substring(IsProcedureModule, 3, 1)
>>>
>>>
>>>
>>> AMB
>>
>>
>>
>

 
 
Delbert





PostPosted: Thu Nov 20 17:42:27 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Yet another:
ORDER BY (3*len(IsProcedureModule))^6

0, 1, 2:
ORDER BY ((3*len(IsProcedureModule))^6) / 7

1, 2, 3:
ORDER BY ((3*len(IsProcedureModule))^6) / 7 + 1


Bye,
Delbert Glass


 
 
The





PostPosted: Fri Nov 21 04:29:10 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column

>order by 7&-ascii(IsProcedureModule)
>order by sin(ascii(IsProcedureModule))
>order by difference(IsProcedureModule,'yenlk')
>
>SK

*Laughs*. You people have too much time on your hands.


--
The Fool.

 
 
Tom





PostPosted: Fri Nov 21 07:50:06 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column This is a multi-part message in MIME format.

------=_NextPart_000_038F_01C3B00C.76257BD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I thought there was something in the water ... ;-)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql






>order by 7&-ascii(IsProcedureModule)
>order by sin(ascii(IsProcedureModule))
>order by difference(IsProcedureModule,'yenlk')
>
>SK

*Laughs*. You people have too much time on your hands.


--
The Fool.

------=_NextPart_000_038F_01C3B00C.76257BD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>I thought there was something in the =
water ...=20
;-)</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV>&nbsp;</DIV>
<DIV>---------------------------------------------------------------<BR>T=
homas=20
A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =
Server=20
Professional<BR>Toronto, ON Canada<BR><A=20
href=3D"http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>"The Fool" &lt;<A =

in message <A=20


Thu, 20 Nov 2003 17:48:16 -0500, Steve Kass &lt;<A=20


7&amp;-ascii(IsProcedureModule)<BR>&gt;order by=20
sin(ascii(IsProcedureModule))<BR>&gt;order by=20
difference(IsProcedureModule,'yenlk')<BR>&gt;<BR>&gt;SK<BR><BR>*Laughs*. =
You=20
people have too much time on your hands.<BR><BR><BR>--<BR>The=20
Fool.<BR></BODY></HTML>

------=_NextPart_000_038F_01C3B00C.76257BD0--

 
 
Alejandro





PostPosted: Fri Nov 21 07:53:13 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Great!!!!!


 
 
Alejandro





PostPosted: Fri Nov 21 07:53:28 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Great!!!!!


 
 
Gerry





PostPosted: Fri Nov 21 07:59:25 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column Thanks again for everyone's help,

Was very helpful.

Thanks again.

I wish I got this kind of response in the .NET newsgroup

Thanks
Gerry




>
> Hi all,
>
> IsProcedureModule Column will only contain a possiable of 3 values
> ('Blank','yes','no')
> I would like to Select all rows ordered by the value 'no' first, then
> 'Blank', then 'yes'
> can someone please help.
>
>
> CREATE TABLE [ModuleList] (
> [ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ModuleOwner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [IsProcedureModule] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> CONSTRAINT [PK_ModuleList] PRIMARY KEY CLUSTERED
> (
> [ModuleOwner]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
> thanks
> Gerry
>
>


 
 
Delbert





PostPosted: Fri Nov 21 13:37:44 CST 2003 Top

SQL Server Developer >> Ordering Rows based on Values in a column > 2^-~len(IsProcedureModule)

Rewritting in hope of getting even better machine code:

order by 2^(1+len(s))

Bye,
Delbert Glass