June 5, 2008 at 12:43 pm
This may be a common problem, but i need some help here.
i have a table like this:
ID, FIRSTNAME, LASTNAME, ORDER_DATE, EXPORT_FLAG
==========================================
1, Fred, Miller, 2007-12-12,1
2,Fred,Miller,2008-02-15,1
3,Monika,Butcher,2008-01-17,1
4,John,Doe,2008-04-01,1
5,Monika,Butcher,2008-02-15,1
I need to set the field EXPORT_FLAG to 0 in all records which are not the first appearance of a person. The result should look like this:
ID, FIRSTNAME, LASTNAME, ORDER_DATE, EXPORT_FLAG
==========================================
1, Fred, Miller, 2007-12-12,1
2,Fred,Miller,2008-02-15,0
3,Monika,Butcher,2008-01-17,1
4,John,Doe,2008-04-01,1
5,Monika,Butcher,2008-02-15,0
"First appearance" means "oldest ORDER_DATE"
Thanks for your Ideas in Advance!
June 5, 2008 at 1:00 pm
Here's a test I did. It should do what you need, once you modify it with your actual table and column names.
create table #T (
ID int identity primary key,
FName varchar(100),
LName varchar(100),
OrderDate datetime,
ExportFlag bit)
insert into #t (fname, lname, orderdate, exportflag)
select 'Sam','Jones',0,1
union all select 'Sam','Jones',1,1
union all select 'Sam','Jones',2,1
union all select 'Sam','Jones',3,1
union all select 'Sam','Jones',4,1
union all select 'Rob','Smith',0,1
union all select 'Rob','Smith',1,1
union all select 'Rob','Smith',2,1
union all select 'Rob','Smith',3,1
union all select 'Rob','Smith',4,1
union all select 'Rob','Smith',5,1
select * from #t
;with CTE (Row, FName, LName, OrderDate, ID, Flag) as
(select row_number() over (partition by fname, lname order by orderdate),
fname, lname, orderdate, id, exportflag
from #t)
update cte
set flag = 0
where row > 1
select * from #t
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2008 at 1:06 pm
And here is my version:
set nocount on;
create table dbo.OrderHdr (
OrderID int,
FirstName varchar(25),
LastName varchar(25),
OrderDate datetime,
ExportFlag bit
);
insert into dbo.OrderHdr values (1, 'Fred', 'Miller', '2007-12-12', 1);
insert into dbo.OrderHdr values (2, 'Fred', 'Miller', '2008-02-15', 1);
insert into dbo.OrderHdr values (3, 'Monika', 'Butcher', '2008-01-17', 1);
insert into dbo.OrderHdr values (4, 'John', 'Doe', '2008-04-01', 1);
insert into dbo.OrderHdr values (5, 'Monika', 'Butcher', '2008-02-15', 1);
select
OrderID,
FirstName,
LastName,
OrderDate,
ExportFlag,
row_number () over (partition by LastName, FirstName order by OrderDate asc) as RowNumber
from
dbo.OrderHdr;
with UpdateData (
OrderID,
RowNumber
) as (
select
OrderID,
row_number () over (partition by LastName, FirstName order by OrderDate asc) as RowNumber
from
dbo.OrderHdr
)
update dbo.OrderHdr set
ExportFlag = 0
from
dbo.OrderHdr oh
inner join UpdateData ud
on (oh.OrderID = ud.OrderID)
where
oh.ExportFlag = 1
and ud.RowNumber > 1;
select
OrderID,
FirstName,
LastName,
OrderDate,
ExportFlag,
row_number () over (partition by LastName, FirstName order by OrderDate asc) as RowNumber
from
dbo.OrderHdr;
drop table dbo.OrderHdr;
set nocount off
😎
June 5, 2008 at 1:31 pm
Thanks a lot!
To be honest, I don't understand a thing in the first solution. I already saw this
...select row_number() over (partition by...
somewhere, but I simply don't know what it does. But - hey- it works :w00t:. The second solution is understandable with just a little bit more than basic SQL knowledge, and you both helped me out of this!
Again, thank you very much!
June 5, 2008 at 1:46 pm
Hit BOL (Books On-Line). If, after reading, you still have questions just post your question on SSC and I'm sure some one will help you out.
😎
June 5, 2008 at 3:01 pm
The row_number function puts a row number on the data. By using the "partition by" part, I make it so the number resets to 1 for each set of first and last names.
If you just run the select inside the parentheses, you'll be able to see what it does, and it will probably make more sense then.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply