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:111111121211221231313313341335133613361I 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>) > 0Be One with the OptimizerTG |
|
|
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:111111121211221231313313341335133613361I 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? |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-03 : 00:38:45
|
Sounds like this to meSELECT <column name here>FROM table tCROSS 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>) )t1WHERE t1.Cnt =1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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)--- 1SELECT *FROM #tmp t1WHERE EXISTS (SELECT * FROM #tmp t2 WHERE t2.number LIKE t1.number+'%' AND t1.Number <> t2.Number)-- 2SELECT 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 ) t2WHERE t2.Number IS NOT NULL; DROP TABLE #tmp; |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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! |
|
|
|