Solved

Help in SQL Query like a puzzle

Posted on 2007-03-29
19
284 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 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

856 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