Solved

Help in SQL Query like a puzzle

Posted on 2007-03-29
19
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +1
19 Comments
 
LVL 143

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 143

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 143

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 143

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 143

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 143

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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