picture is worth a thousand words

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

SQL Server 2000 Forums

Transact-SQL (2000)

picture is worth a thousand words

Author

Topic

big_t
Starting Member

5 Posts

Posted - 2006-10-06 : 01:16:59

I wish I knew how to ask this better, but what I need to do is create a stored procedure that will do several things, but I can't do what I need to do until I can number the rows of a result sub-set. (sorry if my language is not as precise as it shuld be)The numbering will restart with the next subset.

Note: (I have put this " __| "between the vlaues of each record to simulate a table output)

This first select statement and results shows the relationship between my 2 tables.

THe second result set is an example of what I am trying to achieve (in red)

I will need to use the myCounter column to create a string value in a 3rd field that will become part of a batch file that will rename thousands of files.

SELECT PhotoListTest.PhotoFiles, myTestListings.list_no
FROM PhotoListTest INNER JOIN
myTestListings ON PhotoListTest.PhotoFiles LIKE myTestListings.list_no + '%'

PhotoFiles __| list_no
123456_0 __| 123456
123456_1 __| 123456
123456_11 _| 123456
123456_2 __| 123456
123456_3 __| 123456
123456_4 __| 123456
123456_41 _| 123456
123456_45 _| 123456
123456_9 __| 123456
123456_91 _| 123456
123456_90 _| 123456
138761_0 __| 138761
138761_2 __| 138761
138761_3 __| 138761
138761_4 __| 138761
138761_41 _| 138761

PhotoFiles __| myCounter
123456_0 __| 1
123456_1 __| 2
123456_11 _| 3
123456_2 __| 4
123456_3 __| 5
123456_4 __| 6
123456_41 _| 7
123456_45 _| 8
123456_9 __| 9
123456_90 _| 10
123456_91 _| 11
138761_0 __| 1
138761_2 __| 2
138761_3 __| 3
138761_4 __| 4
138761_41 _| 5

I appreciate your help and thank you in advance.

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 01:27:21

Maybe something like this wourld help? I've included an example of an output with RENAME commands suitable for a batch file. Alternatively chuck the PhotoFiles into Excel and manipulate them there!


CREATE TABLE #TEMP
(
    T_ID int IDENTITY NOT NULL,
    T_PhotoFiles varchar(1000) NOT NULL.
    T_list_no int NOT NULL
)

INSERT INTO #TEMP(T_PhotoFiles, T_list_no)
SELECT PhotoListTest.PhotoFiles, myTestListings.list_no 
FROM PhotoListTest INNER JOIN
myTestListings ON PhotoListTest.PhotoFiles LIKE myTestListings.list_no + '%'
ORDER BY myTestListings.list_no, PhotoListTest.PhotoFiles

-- Produce the RENAME statements for a Batch file or somsuch
SELECT 'RENAME ' 
       + '"' + T_PhotoFiles + '.jpg" ' 
       + '"' + T_PhotoFiles + CONVERT(varchar(20), T_ID) + '.jpg"'

Kristen

big_t
Starting Member

5 Posts

Posted - 2006-10-08 : 18:45:44

Thanks Kristen, but I need the counter to start over with the next join. In otherwords, there are 16 records in my example. The first 11 match the first record in the second table and the next 5 match the second. I know how to create a renaming script, what I can not figure out, is how to enumerated the records in one table that match each row in a second table.
There are tousands of these and they need to be done everyday. I can not possibly do this manually.
But I appreciate your input.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-08 : 19:36:12

try this


SELECT 	p.PhotoFiles, l.list_no,
	myCounter	= (select count(*) from PhotoListTest x
				where	x.PhotoFiles like l.list_no + '%'
				and	x.PhotoFiles <= p.PhotoFiles)
FROM 	PhotoListTest p INNER JOIN myTestListings l
ON 	p.PhotoFiles LIKE l.list_no + '%'

Kristen
Test

22859 Posts

Posted - 2006-10-09 : 01:44:12

As a one-off khtan's is easy to code, but I find that approach tends to be slow at runtime.

You could just join the temp table to a subselect of the MIN values for each group and subtract that to give you a "base 0" value for each group; something like:


SELECT 'RENAME ' 
       + '"' + T1.T_PhotoFiles + '.jpg" ' 
       + '"' + T1.T_PhotoFiles + CONVERT(varchar(20), T1.T_ID - T2.MIN_ID + 1) + '.jpg"' 
FROM   #TEMP AS T1
       JOIN
       (
           SELECT T_list_no,
                  [MIN_ID] = MIN(T_ID)
           FROM   #TEMP
           GROUP BY T_list_no
       ) AS T2
           ON T2.T_list_no = T1.T_list_no

Kristen

big_t
Starting Member

5 Posts

Posted - 2006-10-10 : 20:25:14

Thank you both for your help. I have already tried Khtan's methode in a script and put it to use. It worked well. But I appreciate Kristen's input and will compare the two methods.
Thanks again.

Kristen
Test

22859 Posts

Posted - 2006-10-11 : 01:35:12

Thread continued: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73210

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources