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.
| Author |
Topic |
|
Ramnadh
Starting Member
23 Posts |
 Posted - 2004-07-23 : 02:58:38
|
| Hi,I have to make a query like that the row number should be there in everyrow in the resultset. we have to use only the "select" query without using any updates.One method is by doing like this...Select job_desc, (Select Count(*) + 1 FROM jobs BWHERE B.job_desc < A.job_desc) AS RecNoFROM jobs AORDER By job_descBut i am not using the table directly. It was included with the other tables in the query in the StoredProcedure. When i join with the other tables the rownum doesn't get in sequence...and cannot depend on any column value because all the values in all the Columns can be dupliate..Can any one help me.Regards,Ramnadh.Ramnadh |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 04:18:49
|
| You could create a temporary table with an IDENTITY column (additional to the columns that you need) and then INSERT INTO #MyTempTable SELECT ... ORDER BY ... so that they are inserted in sequence.Select job_desc, IDENTITY(1,1) AS RecNoINTO #TEMPFROM jobs AORDER By job_descSELECT * FROM #TEMP ORDER BY RecNoDROP TABLE #TEMPIf the resultset is more than a handful of rows you need to CREATE TABLE #TEMP first and make sure you make the identity column a Primary KeyKristen |
 |
|
|
|
|
|
Query