Rownumber - 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

Transact-SQL (2000)

Rownumber

Author

Topic

Johnyalm
Starting Member

49 Posts

Posted - 2005-12-04 : 02:55:55

I hav e a query like this:

SELECT tblACTORS.name, a_tblTIMESERIES_TYPE.type, AVG(CAST(a_tblTIMESERIES.decimalvalue AS int)) AS average
FROM tblACTORS_KITCODES_GROUPS INNER JOIN
tblACTORS ON tblACTORS_KITCODES_GROUPS.id_actors = tblACTORS.id_actors INNER JOIN
a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type ON
tblACTORS.id_actors = a_tblTIMESERIES.id_actors
WHERE (a_tblTIMESERIES_TYPE.type = 'steps')
GROUP BY tblACTORS.name, a_tblTIMESERIES_TYPE.type
ORDER BY average DESC

It works OK, but I would like to get a NEW column where incremented rownumber is genereated.

Is there a way to do that?

The result of the above query is:

NAME TYPE AVERAGE
Charles steps 12312
Curt steps 11257
Lisa steps 10287

I would like to get

# NAME TYPE AVERAGE
1 Charles steps 12312
2 Curt steps 11257
3 Lisa steps 10287

Johny Alm

www.mirrorgate.com

svicky9
Posting Yak Master

232 Posts

Posted - 2005-12-04 : 03:09:11

If you are using T-sql 2005 you can generate row numbers like

select row_number() over(order by average desc) as rownumber ,tblACTORS.name, a_tblTIMESERIES_TYPE.type, AVG(CAST(a_tblTIMESERIES.decimalvalue AS int)) AS average
FROM tblACTORS_KITCODES_GROUPS INNER JOIN
tblACTORS ON tblACTORS_KITCODES_GROUPS.id_actors = tblACTORS.id_actors INNER JOIN
a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type ON
tblACTORS.id_actors = a_tblTIMESERIES.id_actors
WHERE (a_tblTIMESERIES_TYPE.type = 'steps')
GROUP BY tblACTORS.name, a_tblTIMESERIES_TYPE.type

thanks
vic

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-04 : 03:29:36

also see here
[url]http://www.sqlteam.com/item.asp?ItemID=1491[/url]

-----------------
[KH]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-04 : 06:16:57

and well you can try out the following query ..

SELECT (Select Count(1) From tblACTORS t1 Where t1.Name < tblACTORS.Name),
tblACTORS.name, a_tblTIMESERIES_TYPE.type, AVG(CAST(a_tblTIMESERIES.decimalvalue AS int)) AS average
FROM tblACTORS_KITCODES_GROUPS INNER JOIN
tblACTORS ON tblACTORS_KITCODES_GROUPS.id_actors = tblACTORS.id_actors INNER JOIN
a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type ON
tblACTORS.id_actors = a_tblTIMESERIES.id_actors
WHERE (a_tblTIMESERIES_TYPE.type = 'steps')
GROUP BY tblACTORS.name, a_tblTIMESERIES_TYPE.type
ORDER BY average DESC

if there is any unique columns like DateTime ..etc.. then replace it in the where cluase of the first line of query ..

If there is no unique columns then following query will fail..

then i guess.. you need to create the temp table.. with the identity column ... and pull out the query from that temp table..

Somthing like this ..

SELECT IDENTITY(int, 1,1) Rownum,tblACTORS.name, a_tblTIMESERIES_TYPE.type, AVG(CAST(a_tblTIMESERIES.decimalvalue AS int)) AS average Into #Temp
FROM tblACTORS_KITCODES_GROUPS INNER JOIN
tblACTORS ON tblACTORS_KITCODES_GROUPS.id_actors = tblACTORS.id_actors INNER JOIN
a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type ON
tblACTORS.id_actors = a_tblTIMESERIES.id_actors
WHERE (a_tblTIMESERIES_TYPE.type = 'steps')
GROUP BY tblACTORS.name, a_tblTIMESERIES_TYPE.type

Select * From #Temp

Hope this works for you.. :-)

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-05 : 00:27:05

Where do you want to show these data?
If you use Front End Application use for or While loop to genereate row numbers.
If you use Reports make use of RecordNumber feature. If you try to do this in SQL it will be time consuming if there are thousands of data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources