Solved

Help in SQL Query like a puzzle

Posted on 2007-03-29
19
281 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
The duplicate key value is (<NULL>) 14 46
t-sql month question 8 43
Change part of a string 2 24
Stored Procedure 2 0
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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 …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now