Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Puzzle 0x0B: sorting 1

Status
Not open for further replies.
chrissie1

chrissie1

Programmer
Aug 12, 2002
4,517
BE
I know it's not friday yet. But I know you guys like a little fun so here goes

Data
Code: 
CREATE TABLE #OS 
(
	OS_ID varchar(20) PRIMARY
	, LEVELS int NOT NULL
	, LEVEL_ID int NULL
)

INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1', 1, 1)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1.2', 2, 2)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.2', 1, 2)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.10', 3, 10)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1.3', 2, 3)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1', 2, 1) 
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.1', 3, 1)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.3', 3, 3)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.4', 3, 4)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.5', 3, 5)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.2', 3, 2)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.8', 3, 8)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1.1', 2, 1)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3', 1, 3)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.6', 3, 6)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.7', 3, 7)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.9', 3, 9)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555', 0, NULL)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.11', 3, 11)

And this is what it would look like

Code: 
OS_ID             LEVEL   SUB_LEVEL 
02/05555          0       NULL      
02/05555.1        1       1         
02/05555.1.1      2       1         
02/05555.1.2      2       2         
02/05555.1.3      2       3          
02/05555.2        1       2         
02/05555.3        1       3         
02/05555.3.1      2       1 
02/05555.3.1.1    3       1
02/05555.3.1.2    3       2
02/05555.3.1.3    3       3
02/05555.3.1.4    3       4
02/05555.3.1.5    3       5
02/05555.3.1.6    3       6
02/05555.3.1.7    3       7
02/05555.3.1.8    3       8
02/05555.3.1.9    3       9
02/05555.3.1.10   3       10
02/05555.3.1.11   3       11
[Code]

rules: 1 UDF or less
       1 SELECT statement no less

have fun.


Christiaan Baes
Belgium

[url=http://aspnet20.baesonline.com/Main/Main.aspx]"My new site"[/url] - Me
 
Sort by date Sort by votes
Ya didn't wanted it to be pretty, therefore:
Code: 
set concat_null_yields_null off
select *
from #OS
order by  LTrim(
		right(replicate(' ', 8) + parsename(OS_ID, 4), 8)+
		right(replicate(' ', 8) + parsename(OS_ID, 3), 8)+
		right(replicate(' ', 8) + parsename(OS_ID, 2), 8)+
		right(replicate(' ', 8) + parsename(OS_ID, 1), 8)
	)

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
I should have added to the rules that it must be for an infinte number of levels (lets say 10) and or level_id (lets say 5 chars at most)

@SQLDennis
that will only work for the above
so add this somewhere

Code: 
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.10', 1, 3)

and see it fail.

@vongrunt
what does parsename do?
and it works

I came up with this.

Code: 
SET NOCOUNT ON
GO

CREATE FUNCTION dbo.temp
(
	@OS_ID varchar(20)
)
RETURNS varchar(50) AS
BEGIN
DECLARE @new varchar(50)
DECLARE @counter int 
DECLARE @val varchar(20)
DECLARE @first int 
SET @counter = 0
SET @first = 0
SET @val = ''
SET @new = ''
	WHILE(@counter < LEN(@OS_ID)+1)
	BEGIN
		IF SUBSTRING(@os_id,@counter,1) = '.'
		BEGIN
			if @first = 0
			BEGIN
				SET @new = @val + '.'
				SET @val = ''
				SET @first = 1
			END
			ELSE
			BEGIN
				SET @new = @new + replicate('0', 5 - len(@val)) + @val + '.'
				SET @val = ''
			END
		END
		ELSE
		BEGIN
			SET @val = @val + SUBSTRING(@OS_ID,@counter,1)
		END
		SET @counter = @counter + 1
	END
	if @first = 0
	BEGIN
		SET @new = @val
		SET @val = ''
		SET @first = 1
	END
	ELSE
	BEGIN
		SET @new = @new + replicate('0', 5 - len(@val)) + @val
		SET @val = ''
	END
	RETURN @new
END
GO

CREATE TABLE #OS 
(
	OS_ID varchar(20) PRIMARY KEY
	, LEVELS int NOT NULL
	, LEVEL_ID int NULL
)

INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1', 1, 1)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1.2', 2, 2)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.2', 1, 2)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.10', 3, 10)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1.3', 2, 3)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1', 2, 1) 
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.1', 3, 1)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.3', 3, 3)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.4', 3, 4)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.5', 3, 5)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.2', 3, 2)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.8', 3, 8)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.1.1', 2, 1)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3', 1, 3)         
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.10', 1, 3)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.6', 3, 6)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.7', 3, 7)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.9', 3, 9)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555', 0, NULL)
INSERT INTO #OS (OS_ID,LEVELS, LEVEL_ID) VALUES ('02/05555.3.1.11', 3, 11)

SELECT 		OS_ID
		, LEVELS
		, LEVEL_ID
		, LEFT(OS_ID,LEN(OS_ID)-LEN(CONVERT(varchar(3),LEVEL_ID))-1) as parent_id
		, LEFT(OS_ID,LEN(OS_ID)-LEN(CONVERT(varchar(3),LEVEL_ID))-1) + '.' + replicate('0', 5 - len(convert(char(5),level_ID))) + convert(char(5),level_ID) as parent_id2
		, dbo.temp(OS_ID) as parent_id3
FROM 		#OS 
ORDER BY 	parent_id3
		,LEVELs

DROP TABLE #OS
DROP FUNCTION dbo.temp
GO

Christiaan Baes
Belgium

"My new site" - Me
 
Upvote 0 Downvote
Possible variable-length tokens = problems cannot be solved with simple LEFT() :(

And PARSENAME() fails if number of tokens is greater than 4 (at most three dots).

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
parsename example
declare @var varchar(100)
select @var ='1111.2222.3333.4444'
select parsename(@var,1),parsename(@var,2),parsename(@var,3),parsename(@var,4)

anyway does this work?
select * from #OS
order by parsename(OS_ID,4),parsename(OS_ID,3),parsename(OS_ID,2),2,3

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
> anyway does this work?

In that case problems happen when string has less than 4 tokens :(

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
chrissie1

It looks like each sub-level will be an integer, and you want to sort as though it's an integer even though it is in a vharchar field, so the basic structure is:

String.Integer.Integer.Integer

Sorting should be String, then 1st integer, 2nd integer, etc...

Is this correct?

When we are limited to 4 parts (3 periods), this code should sort properly.

Code: 
Select *
From #OS
Order By ParseName(OS_ID, Levels + 1),
    Convert(int, IsNull(Parsename(OS_ID, Levels), 0)),
    Convert(int, IsNull(Parsename(OS_ID, Levels -1), 0)),
    Convert(int, IsNull(Parsename(OS_ID, Levels -2), 0))

Now we just need to make this work for more sub levels?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
@SQLDennis

Nope

02/05555.10 shold be the last

perhaps I should explain better. 02/05555.1 and 02/05555.2 are children from 02/05555 and 02/05555.1.1 and 02/05555.1.2 are children of 02/05555.1

so they should be ordered on the main level (02/05555) then the first sub level (.1) then the second level (.2) and so on not so difficult untill you get to 10 somewhere in the levels and not to difficult if it is the last level that is more then 10. The trouble start when it's not the last level that is more then 10).

I think for use humans it is very clear how to sort it but it's a bit more difficult in sql. This data comes from an oracle server and te application that uses it doesn't even bother to correctly sort them. But my users like it better if they are sorted the correct way.

And I think I have it sorted now. Not pretty and probably not fast but it works.

Christiaan Baes
Belgium

"My new site" - Me
 
Upvote 0 Downvote
Kewl...

Btw. is ISNULL() stuff necessary? NULLs returned by PARSENAME() already stay on top so...
Code: 
select *,
From #OS
Order By ParseName(OS_ID, Levels + 1),
    Convert(int, Parsename(OS_ID, Levels)),
    Convert(int, Parsename(OS_ID, Levels -1)),
    Convert(int, Parsename(OS_ID, Levels -2))
Of course this assumes that dependent columns LEVELS and OS_ID are always in sync (no UPDATE anomalies).

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
no UPDATE anomalies
Click to expand...

Lets hope not.

so parsename would work untill we get this

02/05555.1.1.1.1.1

And guess what, I checkced the database and it has some with 7 levels. But because I didn't have that in my rules you win vongrunt.

Christiaan Baes
Belgium

"My new site" - Me
 
Upvote 0 Downvote
> Lets hope not.

OK, here is quick check for that:
Code: 
select * 
from #OS
where LEVELS <> len(OS_ID)-len(replace(OS_ID, '.', ''))
No anomalies - no rows returned.

And I'd say George's code is more elegant, assuming all tokens after 1st one are integers (another thing I wasn't sure about).

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
Sorry george I thought that was a vongrunt post.

Yep more sublevels would be nice.

and no, no anomalies, yet.

or should that be

and yes, no anomalies, yet.

Christiaan Baes
Belgium

"My new site" - Me
 
Upvote 0 Downvote
That's ok. [small]I really don't mind vongrunt getting my stars![/small] [wink]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Question
Parameterised sql server query
Replies
8
Views
348
Chriss Miller
C
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
929
pjw001
pjw001
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
519
Chris Miller
Chris Miller
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
753
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
629
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top