Solved

Help in SQL Query like a puzzle

Posted on 2007-03-29
19
283 Views
Last Modified: 2012-05-05
Hi All,

I have one query like a puzzle for me.

I have one table which has many column and one the column has value like

Level
10                  
10.1                
10.1.2              
10.1.2.3            
10.1.2.3.4          
10.1.2.3.4.5        
11.b
11.b.c
11.b.c.d.a                  
12  

I need to create a query and need to consider performace.
How to get result like from the above data..

Result
10.1.2.3.4.5
11.b.c.d.a    
12

How this can be...

first value 10 ,11,12 is the main group and how many "." comes has is the highest depth and i need to find all the value and need to find result like above...
           



0
Comment
Question by:Ved Prakash Agrawal
  • 8
  • 6
  • 3
  • +1
19 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18814933
select t.yourfield
from yourtable t
where not exists ( select null from yourtable i where t.yourfield like i.yourfield + '%' )
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18814936
a small correction (the above would return no records...)
select t.yourfield
from yourtable t
where not exists ( select null from yourtable i where t.yourfield like i.yourfield + '%'  and t.yourfield <> i.yourfield )
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18814972
Hi this resturn me like below result.
10                  
10.1                
10.1.2              
10.1.2.3            
10.1.2.3.4          
10.1.2.3.4.5        
11.b
11.b.c
11.b.c.d.a                
12                  


I want to only three  rows from above example there are thousands are rows there in the table

Result should be :
10.1.2.3.4.5  
11.b.c.d.a  
12
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18814991
Sorry the correct value is in the table Level
10                  
10.1                
10.1.2              
10.1.2.3            
10.1.2.3.4          
10.1.2.3.4.5        
11
11.b
11.b.c
11.b.c.d.a                  
12
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18814999
the result should be
10.1.2.3.4.5
11.b.c.d.a
12
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18815069
what is the name of your field?

select t.yourfield
from level t
where not exists ( select null from level i where t.yourfield like i.yourfield + '%'  and t.yourfield <> i.yourfield )

0
 
LVL 3

Expert Comment

by:popa_duhu
ID: 18815191
i assumed that the table is named "tbl_lvl" and has one field named "lvl"

well...
this is a solution
though, not so clever but works
(i'm thinking for a clever one :D)

create  function dbo.count_dots (@str varchar(200) )
returns int
as
begin
      declare @found bit      
      declare @pos       int
      declare @count int

      set @found = 1
      set @pos = 0
      set @count = 0

      while @found > 0
      begin
            set @pos = charindex('.',@str, @pos + 1)
            if @pos = 0
                  set @found = 0
            else             
            begin
                  set @count = @count + 1            
            end
      end
      
      return @count
end

GO


select lvl, 0 as max_depth, convert(varchar(100),null) as max_lvl into #tmp_lvl from tbl_level where dbo.count_dots(lvl) = 0

update a
set
      max_depth = (select max(dbo.count_dots(lvl)) from tbl_level b where b.lvl like a.lvl + '%')      
from #tmp_lvl a

update a
set a.max_lvl = b.lvl
from #tmp_lvl a join tbl_level b on b.lvl like a.lvl + '%' and dbo.count_dots(b.lvl) = a.max_depth

select max_lvl from #tmp_lvl

drop table #tmp_lvl
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18815366
Hi Angell,
Very strange when i am executing your query i getting different result in SQL 2000 and SQL 2005.

I have same table in Both SQL 2000 and SQL 2005.

when i run in SQL 2000 the output is
10                  
10.1                
10.1.2              
10.1.2.3            
10.1.2.3.4          
10.1.2.3.4.5        
11                  
12                  


when i run in SQL 2005 the output is
10
11
12


0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18815416
maybe in one of the databases it is char, and in the other varchar?


anyhow, I see I reversed the logic, here is the correct (tested) version:

select t.yourfield
from level t
where not exists ( select null from level i where i.yourfield like t.yourfield + '%'  and t.yourfield <> i.yourfield )
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18815419
ps: I tested with SQL 2005
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 18815476
Hey ved17nov,

This might work for you

tbalename:level
column:n

select max(n) from level
group by left(n,2)

assuming that the left 2 of your string is the start point of all groups
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 18815612
Hey angelIII,

thats a nice piece of code.
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18815665
Hi popa_duhu,
its returning
10
11
12

0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18815707
Hi Angell,

I am not able to get the result what i am expecting...
can u post u r result which u r getting....
i can this is a nice code if works for me....


Thanks anyway....
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18815772
Hi Datrias,
this will not work if the data in below format
11.b                
11.c.b              
11.b.c.d.a          

because after main value means 11 everything is a user entry so user can place single character with seprated with "."

any thought...
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18815786
I think this looks more difficult...points icreased..............
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18815795
here is my test script:

create table demo ( yourfield varchar(30))
set nocount on
go
insert into demo values ('10')                  
insert into demo values ('10.1')                
insert into demo values ('10.1.2')              
insert into demo values ('10.1.2.3')          
insert into demo values ('10.1.2.3.4')          
insert into demo values ('10.1.2.3.4.5')        
insert into demo values ('11.b')
insert into demo values ('11.b.c')
insert into demo values ('11.b.c.d.a')                
insert into demo values ('12')                  

go


select t.yourfield
from demo t
where not exists ( select null from demo i where i.yourfield like t.yourfield + '%'  and t.yourfield <> i.yourfield )

go
drop table demo


result:

yourfield
------------------------------
10.1.2.3.4.5
11.b.c.d.a
12



note: if the field is CHAR, change it to varchar.
to workaround ( in case you could not change it):


select t.yourfield
from level t
where not exists ( select null from level i where i.yourfield like rtrim(t.yourfield) + '%'  and t.yourfield <> i.yourfield )
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 18815831
if the value u need is always the last value of the group? I woul add a rolling number and then use something like

select r,n from #level t  where r in (Select max(r) from #level group by left(n,2))
0
 
LVL 3

Expert Comment

by:popa_duhu
ID: 18815997
strange,
i have mssql 2k and works perfectly :D
anyway... it doesn't matter

angel's solution is the best.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question