Row Number Function - Sql 2008

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 - Sql 2008

Author

Topic

sumitsaini001
Starting Member

11 Posts

Posted - 2009-01-05 : 14:42:19

Hi,
I am having problem in SQL 2008
I'm using Row_Number Function in SQl 2008 but it's giving and error.
Below is the snippet:
SELECT in_num , ROW_NUMBER()OVER(PARTITION BY IN_NUM ORDER BY FIRSNAME desc)
FROM pmihead

IN_num : Number( datatype)
FIRSTNAME :String( datatype)

The Error it gives:

Msg 195, Level 15, State 10, Line 1
'ROW_NUMBER' is not a recognized function name.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-05 : 14:48:45

If the database was restored from a 2000 box then you need to re-set the compatibilty level

Be One with the Optimizer
TG

sumitsaini001
Starting Member

11 Posts

Posted - 2009-01-05 : 15:06:57

Hi , thanks , do you how to re-set the compatibilty level
thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-05 : 15:34:13

Assuming 2008 is similar to 2005, in Management Studio | object explorer, navigate to the database, righ click it and select "properties". Select the "options" page. You should see it there.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-05 : 16:12:53

hmmm - SQL Server doesn't seem to mind using ROW_NUMBER() in a 80 database as long as the server is 2005 or higher (unlike PIVOT which does need to have the database compatibility level set to at least 90).

Are you sure you were connecting to your sql server 2008 server when you got that error?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 02:56:15

i guess you were using sql 2008 client tools but were trying to connect to 2000 db. try below and post back result

SELECT @@VERSION

sumitsaini001
Starting Member

11 Posts

Posted - 2009-01-06 : 08:28:56

Hi Visakh,

It is Sql 2000
Version--

Microsoft SQL Server 2000 - 8.00.2050 (Intel X86) Mar 7 2008 21:29:56 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 08:37:04

quote:
Originally posted by sumitsaini001

Hi Visakh,

It is Sql 2000
Version--

Microsoft SQL Server 2000 - 8.00.2050 (Intel X86) Mar 7 2008 21:29:56 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Ok in that case you cant use windowing functions like row_number(). they are available only from sql 2005 onwards.

you could however use like below


SELECT in_num,
(SELECT COUNT(*) FROM pmihead WHERE in_num=t.in_num AND FIRSNAME>=t.FIRSNAME) AS SecondField
FROM pmihead t

sumitsaini001
Starting Member

11 Posts

Posted - 2009-01-06 : 09:35:28

Hi Visakh,

Thanks for the query but i'm still having problem in this query.

it is displaying as
IN_num Secind Field
10583273 1
10583274 1
10583275 1
10583276 1

But it Should be like

IN_num Secind Field
10583273 1
10583274 2
10583275 3
10583276 4

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 09:41:59

quote:
Originally posted by sumitsaini001

Hi Visakh,

Thanks for the query but i'm still having problem in this query.

it is displaying as
IN_num Secind Field
10583273 1
10583274 1
10583275 1
10583276 1

But it Should be like

IN_num Secind Field
10583273 1
10583274 2
10583275 3
10583276 4

thanks

this is different from your original sample where you had firsname also. for above, you need only this


SELECT in_num,
(SELECT COUNT(*) FROM pmihead WHERE in_num<=t.in_num ) AS SecondField
FROM pmihead t

sumitsaini001
Starting Member

11 Posts

Posted - 2009-01-06 : 09:50:16

Hi Visakh,
I'm getting the numbers below
Query:
SELECT distinct t.ctn,
(SELECT COUNT(*) FROM v_alc_cm p WHERE p.ctn <= t.ctn ) AS SecondField
FROM v_alc_cm t
order by t.ctn asc

Output

10230743 8
10236513 13
10465182 22
10535380 28
10536369 31
10561218 41

But is should be

10230743 1
10236513 2
10465182 3
10535380 4
10536369 5
10561218 6

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 09:53:24

can you post sample data from your table. does t.ctn repeat?

sumitsaini001
Starting Member

11 Posts

Posted - 2009-01-06 : 10:04:55

Hi Visakh,
Query:
SELECT t.ctn,
(SELECT COUNT(*) FROM v_alc_cm p WHERE p.ctn = t.ctn ) AS SecondField
FROM v_alc_cm t
order by t.ctn asc
Output Without Distinct:

10230743 8
10230743 8
10230743 8
10230743 8
10230743 8
10230743 8
10230743 8
10230743 8
10236513 5
10236513 5
10236513 5
10236513 5
10236513 5

Query:
SELECT distinct t.ctn,
(SELECT COUNT(*) FROM v_alc_cm p WHERE p.ctn = t.ctn ) AS SecondField
FROM v_alc_cm t
order by t.ctn asc

And Output Using Distinct

10230743 8
10236513 5
10465182 9
10535380 6
10536369 3
10561218 10
10565022 5
10566207 4
10570528 7
10573594 4

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources