SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 levels?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ana_baldaia
Starting Member

Portugal
6 Posts

Posted - 04/18/2013 :  11:36:30  Show Profile  Reply with Quote
Hello,
I dont know if this is the right place to make this question. Maybe it should be in transact sql. Anyway I really need help, so... here it goes and Thanks in advance!
I have something like this:
1
11
111
12
121
122
123
13
133
1334
1335
1336
13361
I need to select records that are roots to others. Like, for ex: 12, 123, 1336. How can I do this.
I have no idea how I can do this.
Thanks again.

TG
Flowing Fount of Yak Knowledge

USA
5946 Posts

Posted - 04/18/2013 :  11:40:52  Show Profile  Reply with Quote
You'll have to be more specific. But perhaps something like this:
where CHARINDEX(<rootValue>, <valueThatHasRoot>) > 0


Be One with the Optimizer
TG
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 04/18/2013 :  11:46:06  Show Profile  Reply with Quote
quote:
Originally posted by ana_baldaia

Hello,
I dont know if this is the right place to make this question. Maybe it should be in transact sql. Anyway I really need help, so... here it goes and Thanks in advance!
I have something like this:
1
11
111
12
121
122
123
13
133
1334
1335
1336
13361
I need to select records that are roots to others. Like, for ex: 12, 123, 1336. How can I do this.
I have no idea how I can do this.
Thanks again.

What is the rule you are using? Why isn't 1 a root? Shouldn't it be the root of 11, 111, 12 etc. Or, why is 123 a root? There are no other numbers that begin with 123; so what is it root of?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/18/2013 :  14:02:15  Show Profile  Reply with Quote
do you've a master table which has list of root values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ana_baldaia
Starting Member

Portugal
6 Posts

Posted - 05/02/2013 :  07:24:15  Show Profile  Reply with Quote
Hello,
Thanks for the answers. I need to select the anterior level to the last one. Dont know if 'm making myself understandable. Every number that has a sucessor and that sucessor is the last one.
123 is not one of theese, it is an error. Ex. 11, 12, 133, 1336.


Thanks.

Ana
Go to Top of Page

ana_baldaia
Starting Member

Portugal
6 Posts

Posted - 05/02/2013 :  07:30:25  Show Profile  Reply with Quote
Sorry. Perhaps is easier if I can select all the last levels.
Thanks,
Ana
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/02/2013 :  07:40:42  Show Profile  Reply with Quote
quote:
Originally posted by ana_baldaia

Hello,
Thanks for the answers. I need to select the anterior level to the last one. Dont know if 'm making myself understandable. Every number that has a sucessor and that sucessor is the last one.
123 is not one of theese, it is an error. Ex. 11, 12, 133, 1336.


Thanks.

Ana


sorry i didnt get the basis for selecting the above. can you explain?

11 & 12 seem to be at 1st level, 133 at 2nd level,1336 3rd level etc so there's no common logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ana_baldaia
Starting Member

Portugal
6 Posts

Posted - 05/02/2013 :  11:28:08  Show Profile  Reply with Quote
Hi,
Is hard to explain this better. I am searching for the number that has a sucessor and that sucessor is the last one. The levels are not important but if one level is the last or not.

I believe that if I can find the last level I can, by the contrary find the ones that are not (which is what I want to find).

For curiosity, this is the portuguese contabilistic system. The last accounts are the only ones that are permitted to have movements. I want to detect if there is any having contabilistic movements that shouldn't have.

Hope you can understand this.

Thanks,
Ana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 05/02/2013 :  12:25:09  Show Profile  Reply with Quote
The question that is not clear to me, and probably to others on the forum who have been trying to help you, is: Given two entries, how do you determine whether one is a successor of the other or not. If you can answer that in a deterministic way the solution would be easy.

You cannot rely on the order of the entries in the table to determine successor/predecessor unless there is another column that you can use to order the results. That is because the data in a table do not have an order. It is an unordered collection.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/03/2013 :  00:38:45  Show Profile  Reply with Quote
Sounds like this to me


SELECT <column name here>
FROM table t
CROSS APPLY (SELECT COUNT(DISTINCT LEN(<column name here>)) AS Cnt
             FROM table
             WHERE <column name here> LIKE t.<column name here> + '%'
             AND LEN(<column name here>) > LEN(t.<column name here>)
             )t1
WHERE t1.Cnt =1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ana_baldaia
Starting Member

Portugal
6 Posts

Posted - 05/13/2013 :  12:45:12  Show Profile  Reply with Quote
Hi Visakh16,
I'm new to this and I can´t fully understand your query but looking at it, it's seems very close of what I need. Can you explain? Thanks.
James K, If I could explain it perhaps I would know where to find what I need. I know it has a sucessor when the "number" (this is treated as text and not numeric) is equal to: another "number" (that has the same sequence of digits) and this last is followed by another digit.
Thanks very much.

Ana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 05/13/2013 :  12:56:29  Show Profile  Reply with Quote
Can you try these queries to see if they are close to what you are looking for? If they are not giving you the right answers, can you post the expected result? You can copy and paste this to an SSMS window and run it to see the results.
CREATE TABLE #tmp (Number VARCHAR(32));
INSERT INTO #tmp VALUES
(1),(11),(111),(12),(121),(122),(123),(13),(133),
(1334),(1335),(1336),(13361)

--- 1
SELECT
	*
FROM #tmp t1
WHERE EXISTS 
(SELECT * FROM #tmp t2 WHERE t2.number LIKE t1.number+'%'
	AND t1.Number <> t2.Number)

-- 2
SELECT
	t1.Number AS [Root],
	t2.Number AS [RootOf]
FROM
	#tmp t1
	CROSS APPLY 
	(
		SELECT MIN(Number) AS Number FROM #tmp t2 
		WHERE t2.number LIKE t1.number+'%' AND t1.Number <> t2.Number
	) t2
WHERE
	t2.Number IS NOT NULL;
	
DROP TABLE #tmp;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/14/2013 :  00:27:17  Show Profile  Reply with Quote
quote:
Originally posted by ana_baldaia

Hi Visakh16,
I'm new to this and I can´t fully understand your query but looking at it, it's seems very close of what I need. Can you explain? Thanks.
James K, If I could explain it perhaps I would know where to find what I need. I know it has a sucessor when the "number" (this is treated as text and not numeric) is equal to: another "number" (that has the same sequence of digits) and this last is followed by another digit.
Thanks very much.

Ana


I'm looking for each record those records whose value contains the record value pattern (111 contains 11 etc). then i take distinct count of cases where i've length of record > length of currently considered record and check if its 1. Only in cases where record is at all but last level this will be true and it should give you what you're looking for

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ana_baldaia
Starting Member

Portugal
6 Posts

Posted - 05/16/2013 :  07:50:12  Show Profile  Reply with Quote
Thanks very much for your answers. James K solution 2 seems to work. I'm working on it now and the results are exacly what I need. Still have to study it to understand how it works.
Thanks very much, you both!

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000