March 25, 2011 at 5:54 pm
I have a table with strings of values which can be of the form
1
1/15/22/21/11
21
18/19
etc.
I want to find the LAST occurrence of each number in the field. That is, the last time 1 was used, 2, 3, etc up to a maximum. 1 has to be distinct from 11 or 21 for instance, which seems to be a problem for patindex.
There is a primary key on each record so I can order it correctly, I just cant figure out how to get the last occurrence of each number when it could be the number by itself or the number embedded in the string with other ones. If there are 20 unique numbers, my result set should have 20 rows.
Any ideas appreciated!
March 25, 2011 at 7:44 pm
You need to use the delimited split function: Here is the latest version of the Delimited Split Function.
Then, use this code:
-- make and populate a table to hold the test data
DECLARE @test TABLE (RowID INT IDENTITY, Col1 varchar(50));
INSERT INTO @test
SELECT '1' UNION ALL
SELECT '1/15/22/21/11' UNION ALL
SELECT '21' UNION ALL
SELECT '18/19' ;
-- build a virtual (inline) tally table.
-- insert the first 20 numbers into @test2 table variable.
DECLARE @test2 TABLE (TestNbr int);
WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
INSERT INTO @test2
SELECT TOP (20) N
FROM TALLY
ORDER BY N;
WITH cte AS
(
-- split the data by the slash
SELECT t.RowID,
t.Col1,
ds.Item
FROM @test t
CROSS APPLY dbo.DelimitedSplit8K(col1, '/') ds
)
-- now for the test numbers, get the max row for each number
SELECT TestNbr, MaxRow
FROM @test2 t2
CROSS APPLY (SELECT MaxRow = MAX(RowID)
FROM cte
WHERE Item = t2.TestNbr) ds;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2011 at 8:37 pm
Thank you. this does work, but on a table with 14K rows (and growing) it's too slow for our needs (about 6 seconds). This needs to be something that responds nearly immediately when the user clicks a box and an ajax call is made to do this. The problem is it selects everything in the table and then evaluates it, when I really need something that selects what i need from the table. I do appreciate your response though.
March 25, 2011 at 9:41 pm
Okay, I must be getting rusty here seeing as I work with Oracle during the day. Can you provide us with the DDL (CREATE TABLE statement) for the table, some sample data (as INSERT INTO statements), and the expected results based on the sample data? We don't need 14K rows, just 5 to 10 as long as it is representative of your problem.
March 25, 2011 at 10:03 pm
Sure:
create table #temp1 (pkID int identity, dms_id varchar(40))
insert into #temp1 values ('1')
insert into #temp1 values ('1/12/14')
insert into #temp1 values ('18/24')
insert into #temp1 values ('14/18/24')
insert into #temp1 values ('17')
result set should be
dms_id pkID
1 2
12 2
14 2
18 4
24 4
17 5
Notice for dms_id 1, the last time it had an entry was pkID 2
interim dms_id's not shown in the list could be null in the result set.. the list of dms_id's i'm looking for will be known.
I've tried some pattern matching on each dms_id as a separate query, its actually pretty fast but not very elegant.
March 25, 2011 at 10:19 pm
David Krauus (3/25/2011)
Thank you. this does work, but on a table with 14K rows (and growing) it's too slow for our needs (about 6 seconds). This needs to be something that responds nearly immediately when the user clicks a box and an ajax call is made to do this. The problem is it selects everything in the table and then evaluates it, when I really need something that selects what i need from the table. I do appreciate your response though.
How does it work if you replace the inline (virtual) tally table with a permanent one? Check out this article for how to make a permanent tally table, and ideas in using it The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url].
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2011 at 11:49 pm
David Krauus (3/25/2011)
Sure:create table #temp1 (pkID int identity, dms_id varchar(40))
insert into #temp1 values ('1')
insert into #temp1 values ('1/12/14')
insert into #temp1 values ('18/24')
insert into #temp1 values ('14/18/24')
insert into #temp1 values ('17')
result set should be
dms_id pkID
1 2
12 2
14 2
18 4
24 4
17 5
Notice for dms_id 1, the last time it had an entry was pkID 2
interim dms_id's not shown in the list could be null in the result set.. the list of dms_id's i'm looking for will be known.
I've tried some pattern matching on each dms_id as a separate query, its actually pretty fast but not very elegant.
To paraphrase, you want the max(pkID) for each value in the list of dms_is's, correct?
Can we see how you implemented the code provided?
March 26, 2011 at 8:05 pm
Wow. Thanks for pointing out everything wrong with my database, when you have absolutely no knowledge of what it is or how it works. And you got all that from a simple sample I typed in in about 30 seconds.
That was not a helpful post, when others here were trying to help.
March 26, 2011 at 8:09 pm
Yes Lynn, that is what I am wanting to end up with.
I have made this work with some pattern matching queries in a while loop. Again, not very elegant but reasonably fast.
March 27, 2011 at 4:49 pm
David Krauus (3/26/2011)
Yes Lynn, that is what I am wanting to end up with.I have made this work with some pattern matching queries in a while loop. Again, not very elegant but reasonably fast.
Please post that code so that we may test against it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2011 at 5:20 pm
Let's try something similar but different. 😀
First, it's impossible to determine if what we've done will have the correct performance without a wad of test data. With that in mind, the following bit of code creates a 14K row wad of data where each row has anywhere from 1 to 10 slash delimited values in the domain from 1 to 10,000. :w00t:
--===== Conditionally drop the test table to make reruns easier.
-- Note that this is NOT a part of the solution.
IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on the fly.
-- Again, note that this is NOT a part of the solution.
SELECT TOP 14000
RowNum = IDENTITY(INT,1,1),
RandomSlashData = STUFF(
(
SELECT TOP (ABS(CHECKSUM(NEWID()))%10+1)
'/' + CAST(ABS(CHECKSUM(NEWID()))%10000+1 AS VARCHAR(10))
FROM sys.All_Columns ac3
WHERE ac1.Object_ID = ac3.Object_ID
FOR XML PATH('')
)
,1,1,'')
INTO #TestTable
FROM sys.All_Columns ac1
CROSS JOIN sys.All_Columns ac2
;
--===== Show what the data looks like...
-- Again, this is NOT a part of the solution.
SELECT TOP 100 *
FROM #TestTable
;
Now, using the dbo.DelimitedSplit8K code that Wayne posted a link for, let's solve the problem...
--===== Solve the problem (Yes, this IS a part of the solution)
SELECT LatestRowNum = MAX(tt.RowNum),
Item = CAST(split.Item AS INT)
FROM #TestTable tt
CROSS APPLY dbo.DelimitedSplit8k(tt.RandomSlashData,'/') split
GROUP BY split.Item
ORDER BY Item
;
That takes less than two seconds on my tired, ol' 9 year old single 1.8GHz CPU. It should run much faster on a server.
Of course, we need to verify what has happened. Take note of where the number 1 occured as the "LatestRowNum" in the code above and compare it to where it shows up in all the rows in the following code...
--===== Show where all the 1's occured for comparison against
-- what was returned above.
-- Again, this is NOT a part of the solution. It's just for verification.
SELECT *
FROM #TestTable
WHERE '/'+RandomSlashData+'/' LIKE '%/1/%' --<<< Change the number you want to check here
ORDER BY RowNum DESC
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2011 at 7:38 pm
Thanks Jeff, (and others) for taking your time to try to help me.
I am out of town for a few days and wont be able to try that test until wednesday. I will do so and repost to this thread. Thank you again!
March 31, 2011 at 7:42 pm
David Krauus (3/27/2011)
Thanks Jeff, (and others) for taking your time to try to help me.I am out of town for a few days and wont be able to try that test until wednesday. I will do so and repost to this thread. Thank you again!
Did you get a chance to try any of this?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply