row_number() function in 2000

Question

row_number() function in 2000

jameslester78-958147

Old Hand

Points: 342
More actions
March 10, 2009 at 6:18 am

#128550

Ive been asked to produce a recordset in sql2000 :angry: and i would normally use the row_number function in 2005 to achieve this goal of selecting the earliest record for each reference number.
Ive posted my 2005 solution and set up below, I have no idea how to replicate in 2000...any ideas?
--create table
CREATE TABLE [dbo].[jlrownumber](
[ref] [smallint] NOT NULL,
[userref] [smallint] NOT NULL,
[date] [datetime] NOT NULL)
--test data, execute the print statements then cut and paste the result in the query pane
print 'insert into jlrownumber'
print 'select 1,1,''01 Jan 2009'' Union '
print 'select 1,2,''02 Jan 2009'' Union '
print 'select 1,1,''03 Jan 2009'' Union '
print 'select 2,3,''04 Jan 2009'' Union '
print 'select 2,2,''05 Jan 2009'' Union '
print 'select 1,1,''01 Jan 2009'''
--2005 solution
with cte as (
select*
,row_number() over (partition by ref order by date) as RN
fromjlrownumber)
select ref,userref,date
from cte
where rn = 1
--2000 solution
???????

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

slimchance99 SSCrazy Points: 2661 More actions · Answer 1

Your example table has no unique primary key, but you could get the same result using an aggregate query as a derived table.

i.e. select min([date]) group by [ref] will give you the earliest record for each [ref]. If your table has a primary key, you can use the aggregate query as a derived table to return the key of the earliest record and join it back to the main table to get the rest of the fields for the earliest row.

Tim

jameslester78-958147 Old Hand Points: 342 More actions · Answer 2

something like this?

select ref,min(date) as date

into #cte

from jlrownumber

group by ref

select jl.*

from jlrownumber jl

inner join #cte cte

on cte.ref = jl.ref and cte.date = jl.date

I guess this would work if there are no duplicated refs and dates but I think that there might be...

let me reask the inital question

--create table

CREATE TABLE [dbo].[jlrownumber] (

[smallint] NOT NULL ,

[ref] [smallint] NOT NULL ,

[userref] [smallint] NOT NULL ,

[date] [datetime] NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[jlrownumber] ADD

CONSTRAINT [PK_jlrownumber] PRIMARY KEY CLUSTERED

(

) ON [PRIMARY]

GO

--test data, execute the print statements then cut and paste the result in the query pane

print 'insert into jlrownumber'

print 'select 1,1,1,''01 Jan 2009'' Union '

print 'select 2,1,1,''03 Jan 2009'' Union '

print 'select 3,1,2,''02 Jan 2009'' Union '

print 'select 4,2,2,''05 Jan 2009'' Union '

print 'select 5,2,4,''04 Jan 2009'' Union '

print 'select 6,2,3,''04 Jan 2009'''

--2005 solution

with cte as (

select*

,row_number() over (partition by ref order by date,userref) as RN

fromjlrownumber)

select ,ref,userref,date

from cte

where rn = 1

--2000 solution

???????

slimchance99 SSCrazy Points: 2661 More actions · Answer 3

For your duplicate rows - what is the business rule to determine the earliest record? If the date and ref are identical, how do you determine that one is 'earlier' than the other?

Tim

jameslester78-958147 Old Hand Points: 342 More actions · Answer 4

Good question and one which Ill need to find the answer to. For the purpose of the example though can we say its the record the lowest userid.

Ill go back and edit the example so we can get a definate answer.

slimchance99 SSCrazy Points: 2661 More actions · Answer 5

Something like this will give you the result. I would assume your real data includes some determinant for the 'earliest' record in a case where the date is the same (time??).

SELECT distinct

jlrownumber.ref,

jlrownumber.userref,

jlrownumber.[date] AS [earliest]

FROM

jlrownumber

INNER JOIN ( SELECT

ref,

MIN(date) AS Expr1

FROM

jlrownumber AS jlrownumber_1

GROUP BY

ref ) AS derivedtbl_1

ON jlrownumber.ref = derivedtbl_1.ref

AND jlrownumber.[date] = derivedtbl_1.Expr1

Tim

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

http://www.sqlservercentral.com/Forums/Topic672929-145-1.aspx

Beware the easy way... it's a trap... see my post on the thread above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Aram Koukia SSCommitted Points: 1521 More actions · Answer 7

hi

i thing the solution in sql2000 is that you may need to use a temp table and try somthing like this for generating a row_number

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable

[font="Courier New"]Aram Koukia: http://www.koukia.ca[/font]

jameslester78-958147 Old Hand Points: 342 More actions · Answer 8

Jeff Moden (3/10/2009)
http://www.sqlservercentral.com/Forums/Topic672929-145-1.aspx
Beware the easy way... it's a trap... see my post on the thread above.

Thanks Jeff your post in that thread is really useful 🙂