?
Solved

Help in SQL Query like a puzzle

Posted on 2007-03-29
19
Medium Priority
?
292 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 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