row number problem - SQL Server Forums

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

SQL Server Development (2000)

row number problem

Author

Topic

joemama
Posting Yak Master

113 Posts

Posted - 2007-06-10 : 02:58:13

here is the query

SET NOCOUNT ON

CREATE TABLE #RowNumber (
RowNumber int IDENTITY (1, 1),
StuEnterID INT)

INSERT #RowNumber (StuEnterID)
SELECT StuEnterID
FROM dbo.WStudy S
WHERE StuEnterID = '50484'

SELECT RowNumber, S.StuID, S.StuTitle, S.StuEnterID
FROM #RowNumber R JOIN dbo.WStudy S
ON r.StuEnterID = s.StuEnterID
ORDER BY RowNumber

DROP TABLE #RowNumber

SET NOCOUNT OFF

it does add row number to table HOWEVER it looks like this

1 1085 eeee 50484
1 1086 dddd 50484
2 1085 eeee 50484
2 1086 dddd 50484
3 1088 eeeeee 50484
3 1089 s 50484

AND i WANT IT TO LOOK LIKE THIS

1 1085 eeee 50484
2 1085 eeee 50484
3 1088 eeeeee 50484

any help would be greatly appreciated

Kristen
Test

22859 Posts

Posted - 2007-06-10 : 03:40:00

This maybe?


INSERT #RowNumber (StuEnterID)
SELECT StuEnterID
FROM dbo.WStudy S
WHERE StuEnterID = '50484' 
ORDER BY S.StuID, S.StuTitle, S.StuEnterID

Kristen

joemama
Posting Yak Master

113 Posts

Posted - 2007-06-10 : 03:52:06

actually i figured it out...it is

INSERT INTO #RowNumber (StuEnterID)

quote:

Originally posted by Kristen

This maybe?


INSERT #RowNumber (StuEnterID)
SELECT StuEnterID
FROM dbo.WStudy S
WHERE StuEnterID = '50484' 
ORDER BY S.StuID, S.StuTitle, S.StuEnterID

Kristen

Kristen
Test

22859 Posts

Posted - 2007-06-10 : 06:34:33

Its is? Well if that's the only change that's needed I'm a Chinaman ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 09:11:19

1 where do you want to show data? If you use front end application, you can easily do numbering there
2 Kristen's query will work fine as well

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources