probably a n00bie sort question

Question

Post reply

probably a n00bie sort question

Christopher Klein

SSCrazy

Points: 2648
More actions
February 27, 2004 at 12:37 pm

#84303

Hi there,
I have a table with 3 columns of data (m,b,l).
10,10,1
10,10,2
10A,10,2
10,10,3
How the heck can I get an ordered by m,b,l sort and NOT have the 3rd row (10A,10,2) show up at the bottom?
thanks!
Chris

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

One way would be

if object_id('test_q') is not null

drop table test_q

go

create table test_q(

m char(3),

b int,

l int

)

insert into test_q (m,b,l) values('10',10,1)

insert into test_q (m,b,l) values('10',10,2)

insert into test_q (m,b,l) values('10A',10,2)

insert into test_q (m,b,l) values('10',10,3)

select * from test_q order by m desc,b,l

m b l

---- ----------- -----------

10A 10 2

10 10 1

10 10 2

10 10 3

(4 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 2

I may need more details for the data types you are using

but this is an ex

Select m,b,l

From TableName

Order By Left(m,2),b,l

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

select * from test_q order by left(m,2),b,l

results in

m b l

---- ----------- -----------

10 10 1

10 10 2

10A 10 2

10 10 3

(4 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 4

Yes, (10A, 10, 2) is NOT at the bottom

isn't that the purpose ?

* Noel

Christopher Klein SSCrazy Points: 2648 More actions · Answer 5

Frank is close but I unfortunately did not think the entire post thru before posting...

Typically vchrMap,vchrBlock,vchrLot are all varchar(10). The alpha character(s) can be any position but the first.

I came up with the following but its a nightmare with the cursor... especially when its run against several hundred thousand rows.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

create PROCEDURE sp_MBL_SORT

AS

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM dbo.sysobjects

WHERE id = object_id(N'[dbo].[MBL]')

AND objectproperty(id,N'IsTable') = 1)

CREATE TABLE [dbo].[MBL] (

[ID] [int] NOT NULL ,

[vchrMap] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[vchrBlock] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[vchrLot] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[M] [decimal](24, 6) NOT NULL ,

[decimal](24, 6) NOT NULL ,

[L] [decimal](24, 6) NOT NULL

) ON [PRIMARY]

IF EXISTS (SELECT * FROM dbo.sysobjects

WHERE id = object_id(N'[dbo].[MBL_MakeTable]')

AND objectproperty(id,N'IsView') = 1)

DROP VIEW [dbo].[MBL_MakeTable]

declare @sql varchar(1000)

select @sql= 'CREATE VIEW MBL_MakeTable

AS

SELECT

intMasterAcctID as ID,

vchrMap,

vchrBlock,

vchrLot,

0 as M,

0 as B,

0 as L

FROM

Properties'

exec(@sql)

DELETE MBL

INSERT INTO MBL SELECT * FROM MBL_MakeTable

DECLARE @Map varchar(10), @Block varchar(10), @Lot varchar(10), @position int, @string char(8)

DECLARE @newMap varchar(10), @newBlock varchar(10), @newLot varchar(10)

SET @position = 1

SET @newMap = ''

SET @newBlock = ''

SET @newLot = ''

DECLARE MBL_cursor CURSOR FOR

SELECT vchrMap, vchrBlock, vchrLot FROM MBL

FOR UPDATE

OPEN MBL_cursor

FETCH NEXT FROM MBL_cursor

INTO @Map, @Block, @Lot

WHILE @@FETCH_STATUS = 0

BEGIN

---------------------------------------------------------------------------

SET @string = @Map

WHILE @position <= DATALENGTH(rtrim(@string))

BEGIN

IF NOT ISNUMERIC(CHAR(ASCII(SUBSTRING(@string, @position, 1)))) = 1

BEGIN

SET @newMap = SUBSTRING(@string, 1, @position-1)

BREAK

END

ELSE

SET @newMap = SUBSTRING(@string, 1, @position)

SET @position = @position + 1

END

SET @position = 1

---------------------------------------------------------------------------

SET @string = @Block

WHILE @position <= DATALENGTH(rtrim(@string))

BEGIN

IF NOT ISNUMERIC(CHAR(ASCII(SUBSTRING(@string, @position, 1)))) = 1

BEGIN

SET @newBlock = SUBSTRING(@string, 1, @position-1)

BREAK

END

ELSE

SET @newBlock = SUBSTRING(@string, 1, @position)

SET @position = @position + 1

END

SET @position = 1

---------------------------------------------------------------------------

SET @string = @Lot

WHILE @position <= DATALENGTH(rtrim(@string))

BEGIN

IF NOT ISNUMERIC(CHAR(ASCII(SUBSTRING(@string, @position, 1)))) = 1

BEGIN

SET @newLot = SUBSTRING(@string, 1, @position-1)

BREAK

END

ELSE

SET @newLot = SUBSTRING(@string, 1, @position)

SET @position = @position + 1

END

SET @position = 1

---------------------------------------------------------------------------

UPDATE MBL

SET M = @newMap, B = @newBlock, L = @newLot

FROM MBL

WHERE CURRENT OF MBL_cursor

SET @position = 1

SET @newMap = ''

SET @newBlock = ''

SET @newLot = ''

FETCH NEXT FROM MBL_cursor

INTO @Map, @Block, @Lot

END

CLOSE MBL_cursor

DEALLOCATE MBL_cursor

IF EXISTS (SELECT * FROM dbo.sysobjects

WHERE id = object_id(N'[dbo].[MBL_Sorted]')

AND objectproperty(id,N'IsView') = 1)

DROP VIEW [dbo].[MBL_Sorted]

select @sql= '

CREATE VIEW MBL_Sorted

AS

SELECT TOP 100 PERCENT *,

vchrMap + ''-'' + vchrBlock + ''-'' + vchrLot AS MBL

FROM MBL

ORDER BY M, B, L'

exec(@sql)

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

-=--=-=-==

Ultimately I'm looking for anyway around the above mess. Sorry for the earlier posting and wasting peoples time.

Chris

noeld SSC Guru Points: 96590 More actions · Answer 6

Can you tell me if this is what you want:

Select m, b,l

From Table

Order By

CAST((CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN M

ELSE LEFT(M,PATINDEX('%[A-Z]%',M)-1)END ) AS INT )

, (CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN NULL

ELSE RIGHT(M, LEN(M)-PATINDEX('%[A-Z]%',M)+1)END )

,CAST((CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN B

ELSE LEFT(B,PATINDEX('%[A-Z]%',B)-1)END ) AS INT )

, (CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN NULL

ELSE RIGHT(B, LEN(B)-PATINDEX('%[A-Z]%',B)+1)END )

,CAST((CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN L

ELSE LEFT(L,PATINDEX('%[A-Z]%',L)-1)END ) AS INT )

, (CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN NULL

ELSE RIGHT(L, LEN(L)-PATINDEX('%[A-Z]%',L)+1)END )

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

set nocount on

if object_id('test_q') is not null

drop table test_q

go

create table test_q(

m char(3),

b char(3),

l char(3)

)

insert into test_q (m,b,l) values('10','10','1')

insert into test_q (m,b,l) values('10','10','2')

insert into test_q (m,b,l) values('10A','10','2')

insert into test_q (m,b,l) values('10','10','3')

Select m, b,l

From test_q

Order By

CAST((CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN M

ELSE LEFT(M,PATINDEX('%[A-Z]%',M)-1)END ) AS INT )

, (CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN NULL

ELSE RIGHT(M, LEN(M)-PATINDEX('%[A-Z]%',M)+1)END )

,CAST((CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN B

ELSE LEFT(B,PATINDEX('%[A-Z]%',B)-1)END ) AS INT )

, (CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN NULL

ELSE RIGHT(B, LEN(B)-PATINDEX('%[A-Z]%',B)+1)END )

,CAST((CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN L

ELSE LEFT(L,PATINDEX('%[A-Z]%',L)-1)END ) AS INT )

, (CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN NULL

ELSE RIGHT(L, LEN(L)-PATINDEX('%[A-Z]%',L)+1)END )

set nocount off

m b l

---- ---- ----

10 10 1

10 10 2

10 10 3

10A 10 2

Chris, can you post some underlying data? Not that I fully understand what's going on

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 8

That was intended to

order By

numeric of m

, apha of m

, numeric of b

, alpha of b

, numeric of l

, alpha of l

Should you need order by the numerics only you can

Select m, b,l

From test_q

Order By

CAST((CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN M

ELSE LEFT(M,PATINDEX('%[A-Z]%',M)-1)END ) AS INT )

--, (CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN NULL

--ELSE RIGHT(M, LEN(M)-PATINDEX('%[A-Z]%',M)+1)END )

,CAST((CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN B

ELSE LEFT(B,PATINDEX('%[A-Z]%',B)-1)END ) AS INT )

--, (CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN NULL

--ELSE RIGHT(B, LEN(B)-PATINDEX('%[A-Z]%',B)+1)END )

,CAST((CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN L

ELSE LEFT(L,PATINDEX('%[A-Z]%',L)-1)END ) AS INT )

--, (CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN NULL

--ELSE RIGHT(L, LEN(L)-PATINDEX('%[A-Z]%',L)+1)END )

But the data provided does not make it very clear

* Noel

Christopher Klein SSCrazy Points: 2648 More actions · Answer 9

Actually thats just about perfect for what we need. Gets rid of the need for that damned cursor and almost all of that code. The 10A 10 2 should be returned as the 3rd record. Its an unfortunate sorting scheme that municipalitys use for keeping track of propertys.

Thank you very much for your help.

Chris

noeld SSC Guru Points: 96590 More actions · Answer 10

As I posted before you can get exactly what you asked for. Just change the order on the order by clause as you please

The line will be retured on the Third record if you use this order:

Select m, b,l

From test_q

Order By

CAST((CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN M

ELSE LEFT(M,PATINDEX('%[A-Z]%',M)-1)END ) AS INT ) --Numeric m

,CAST((CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN B

ELSE LEFT(B,PATINDEX('%[A-Z]%',B)-1)END ) AS INT ) -- Numeric b

,CAST((CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN L

ELSE LEFT(L,PATINDEX('%[A-Z]%',L)-1)END ) AS INT ) -- Numeric l

, (CASE WHEN PATINDEX('%[A-Z]%',B) = 0 THEN NULL

ELSE RIGHT(B, LEN(B)-PATINDEX('%[A-Z]%',B)+1)END ) --ALpha m

, (CASE WHEN PATINDEX('%[A-Z]%',M) = 0 THEN NULL

ELSE RIGHT(M, LEN(M)-PATINDEX('%[A-Z]%',M)+1)END ) --ALpha b

, (CASE WHEN PATINDEX('%[A-Z]%',L) = 0 THEN NULL

ELSE RIGHT(L, LEN(L)-PATINDEX('%[A-Z]%',L)+1)END ) -- Alpha l

* Noel