Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 levels?

Author  Topic 

ana_baldaia
Starting Member

6 Posts

Posted - 2013-04-18 : 11:36:30
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-18 : 11:40:52
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-18 : 11:46:06
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

52326 Posts

Posted - 2013-04-18 : 14:02:15
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

6 Posts

Posted - 2013-05-02 : 07:24:15
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

6 Posts

Posted - 2013-05-02 : 07:30:25
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

52326 Posts

Posted - 2013-05-02 : 07:40:42
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

6 Posts

Posted - 2013-05-02 : 11:28:08
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-02 : 12:25:09
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

52326 Posts

Posted - 2013-05-03 : 00:38:45
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

6 Posts

Posted - 2013-05-13 : 12:45:12
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-13 : 12:56:29
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

52326 Posts

Posted - 2013-05-14 : 00:27:17
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

6 Posts

Posted - 2013-05-16 : 07:50:12
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
   

- Advertisement -