March 10, 2009 at 6:18 am
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
from jlrownumber)
select ref,userref,date
from cte
where rn = 1
--2000 solution
???????
March 10, 2009 at 7:22 am
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
March 10, 2009 at 8:18 am
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
from jlrownumber)
select ,ref,userref,date
from cte
where rn = 1
--2000 solution
???????
March 10, 2009 at 8:31 am
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
March 10, 2009 at 8:36 am
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.
March 10, 2009 at 8:39 am
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
March 10, 2009 at 11:46 pm
http://qa.sqlservercentral.com/Forums/Topic672929-145-1.aspx
Beware the easy way... it's a trap... see my post on the thread above.
--Jeff Moden
March 11, 2009 at 6:03 am
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
March 11, 2009 at 6:09 am
Jeff Moden (3/10/2009)
http://qa.sqlservercentral.com/Forums/Topic672929-145-1.aspxBeware the easy way... it's a trap... see my post on the thread above.
Thanks Jeff your post in that thread is really useful 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply