ROW_NUMBER function - 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

General SQL Server Forums

New to SQL Server Programming

ROW_NUMBER function

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-17 : 03:13:46

ROW_NUMBER function is not available in SQL Server version 8.0, any other alternative

thanks
krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 03:20:47

A correlated subquery, or use a temp table with identity column.
It's hard to write something specific without knowing more about your problem.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 03:21:46

Acceptable solution is posted for you here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123848

E 12°55'05.63"
N 56°04'39.26"

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-17 : 03:24:25

Hi peso
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123848[/url]

you had given me a solution for my query. that worked perfectly in development machine, but in the production server its an old sql server (v8.0, mine is v9.XX)

please can you modify your query so that it will work in older version too

i am sorry, i am bit new to this sql, please help me

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-17 : 03:27:23

i think its a new feature in SQL server 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 05:06:55

quote:
Originally posted by krishna_yess

Hi peso
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123848[/url]

you had given me a solution for my query. that worked perfectly in development machine, but in the production server its an old sql server (v8.0, mine is v9.XX)

please can you modify your query so that it will work in older version too

i am sorry, i am bit new to this sql, please help me

See aprichard's reply in that thread

Madhivanan

Failing to plan is Planning to fail

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-17 : 05:47:06

I saw that, but he is inserting same values again. is there a way i can select from the original table and insert into new temp table. some thing like

select into  table

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-17 : 06:21:42

its working fine but one problem :(

i needed ONE row which has latest date. but the query will list all the rows which has a latest date.

orignila talbe [ i have other fields too. so i cannot use DISTINCT keyword]


date	Project
17-Apr-09	DTV Humax R15
17-Apr-09	DTV Humax R15
13-Apr-09	t
14-Apr-09	t
14-Apr-09	t
17-Apr-09	DTV Humax R15
17-Apr-09	DTV Humax R15
17-Apr-09	DTV Humax R15
15-Apr-09	dunn
15-Apr-09	dunn
17-Apr-09	DTV Humax R15
17-Apr-09	DTV Humax R15
17-Apr-09	DTV Humax R15

query


select * from table11 a
inner join 
(select projectname, Max(Date) Date from table11
group by projectname) b on a.projectname=b.projectname and a.Date=b.Date 

result::

date	        Project	 Project	date
14-Apr-09	t	        t	        14-Apr-09
14-Apr-09	t	        t	        14-Apr-09
17-Apr-09	dunn	        dunn	        17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09

i just need only one row for each product

like this


Project	date	Project	date
			
14-Apr-09	t	t	14-Apr-09
17-Apr-09	dunn	dunn	17-Apr-09
17-Apr-09	DTV Humax R15	DTV Humax R15	17-Apr-09

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 07:14:52

select project,max(date) as date from table11
group by project

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-17 : 08:33:57

if u want to generate row numbers tahen u must have a column which uniquely identify data ... if u have any this type of column like id or sid or something like that,,, then,, you can generate row numbers in your sql like this.....

see that......
this using correlated subquery to do so..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117964

Here may be u find some help..
or just use madhivanan's sir query....
Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources