how can I include the row number in a stored procedure 1

[deanbri75]

I'm doing a simple select query from a SQL database using a stored procedure.

SELECT TOP 100 ID, FirstName, LastName, MailedDate
FROM dbo.Table
ORDER BY MailedDate

What I can't figure out how to do is include the row number as a field in the stored procedure.

I did some searching and saw how to use @@ROWCOUNT but that didn't work. It returns the same value (in my case 100) for each row. I wanted it to increment like the first row would have 1 as a value, second row would have 2, etc.

Any insight on this is appreciated.

 

[RoyVidar]

Probably a drain on resources, and needs MailedDate to be unique, but try

[tt]SELECT TOP 100
t.ID,
t.FirstName,
t.LastName,
t.MailedDate,
(
SELECT
Count(*)
FROM
dbo.Table s
WHERE
s.MailedDate <= t.MailedDate
)
FROM
dbo.Table t
ORDER BY
t.MailedDate[/tt]

Perhaps for future questions relating to SQL Server Stored Procedures, try the SQL Server programming forum (forum183)

Roy-Vidar

 

[fredericofonseca]

Why do you need it like that? What are you trying to accomplish?

If you are loading it into a grid or a record set you can easly do it on the programming side, and in terms of SQL table it won't help you much as it will not serve as a key to update the records.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[deanbri75]

Thanks for the quick replies. I tried the query you suggested but it have the sameresults as using @@rowcount. Each row showed the total count (in my case 100) for each row instead of incrementing it like 1, 2, 3.

I'm not using it as a key to update records.

thanks again

 

[PHV]

SELECT TOP 100 Count(*) AS RowNumber, A.ID, A.FirstName, A.LastName, A.MailedDate
FROM dbo.Table A, dbo.Table B
WHERE A.MailedDate & A.ID >= B.MailedDate & B.ID
GROUP BY A.ID, A.FirstName, A.LastName, A.MailedDate
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[deanbri75]

PHV, thanks for that. It almost worked but I got an error: Invalid operator for data type. Operator equals boolean AND, type equals smalldatetime. Statements could not be prepared.

So, I altered it slightly in an ignorant effort to make it work.

SELECT TOP 100 Count(*) AS RowNumber, A.SSN, A.FirstName, A.LastName, A.MailedDate
FROM dbo.LetterFile A, dbo.LetterFile B
WHERE A.MailedDate >= B.MailedDate and  A.SSN >= B.SSN
GROUP BY A.SSN, A.FirstName, A.LastName, A.MailedDate
ORDER BY 1

And while this was close, it didn't work either. I ended up with different numbers for the rows but it wasn't sequential, it was more like 1,1,1,2,2,3,3,3,3,4,4.

thanks,
brian

 

[PHV]

Try to play with something like CAST or CONVERT.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Or PHV's suggestion, only replace the concatenation operator & (ampersand) with +, though you may have to cast.

Roy-Vidar

 

[deanbri75]

Yay! the following SQL worked for me:

SELECT     TOP 100 COUNT(*) AS RowNumber, A.SSN, A.FirstName, A.LastName, A.MailedDate
FROM         dbo.LetterFile A INNER JOIN
                      dbo.LetterFile B ON CAST(A.MailedDate AS varchar(10)) + A.SSN >= CAST(B.MailedDate AS varchar(10)) + B.SSN
GROUP BY A.SSN, A.FirstName, A.LastName, A.MailedDate
ORDER BY COUNT(*)

It took almost 3 minutes to run in Query Analyzer. When I tried to run the stored procedure in my access project I get a "timeout expired" error.

Again, thank you guys so much for your help!