best/fastest why to do this - high level

Hello
Here is table
FileID FileType DateCreated Active
1 Revised 1-Jan-06 1
1 Revised 2-Jan-06 1
1 Created 3-Jan-06 1
2 Created 4-Jan-06 1
2 Revised 5-Jan-06 1
3 Created 6-Jan-06 1
3 Revised 7-Jan-06 1
3 Revised 8-Jan-06 1
3 Revised 9-Jan-06 1
and i want to set old records Active column 0. An old record is defined as
sorting by FileID ASC, FileType DSC, DateCreated DSC. Any record beside the
first one is old. I.e.
FileID FileType DateCreated Active
1 Revised 2-Jan-06 1
1 Revised 1-Jan-06 0
1 Created 3-Jan-06 0
2 Revised 5-Jan-06 1
2 Created 4-Jan-06 0
3 Revised 9-Jan-06 1
3 Revised 8-Jan-06 0
3 Revised 7-Jan-06 0
3 Created 6-Jan-06 0
now this table will keep growing and growing so here is my idea.
1. Select FileID's that have a active count > 1 where active = 1. The reason
i do this is because if i have a fileID that has not been updated in awhile
then i do not want to waste time reupdating these records.
2. get the latest records for the FileID's found in step one. I would have
to use a subquery to get the latest FileID for each found in step 1.
3. update records that to not equal the records found in step 2 where active
= 1.
Wanted to know ppls thoughs on my approch and if there is a better way of
doing it.
ThanksTry,
update t1
set active = 0
where exists (
select *
from t1 as t2
where t2.FileID = t1.FileID and t2.DateCreated > t1.DateCreated
)
go
AMB
"John Smith" wrote:

> Hello
> Here is table
> FileID FileType DateCreated Active
> 1 Revised 1-Jan-06 1
> 1 Revised 2-Jan-06 1
> 1 Created 3-Jan-06 1
> 2 Created 4-Jan-06 1
> 2 Revised 5-Jan-06 1
> 3 Created 6-Jan-06 1
> 3 Revised 7-Jan-06 1
> 3 Revised 8-Jan-06 1
> 3 Revised 9-Jan-06 1
>
> and i want to set old records Active column 0. An old record is defined as
> sorting by FileID ASC, FileType DSC, DateCreated DSC. Any record beside th
e
> first one is old. I.e.
> FileID FileType DateCreated Active
> 1 Revised 2-Jan-06 1
> 1 Revised 1-Jan-06 0
> 1 Created 3-Jan-06 0
> 2 Revised 5-Jan-06 1
> 2 Created 4-Jan-06 0
> 3 Revised 9-Jan-06 1
> 3 Revised 8-Jan-06 0
> 3 Revised 7-Jan-06 0
> 3 Created 6-Jan-06 0
>
> now this table will keep growing and growing so here is my idea.
> 1. Select FileID's that have a active count > 1 where active = 1. The reas
on
> i do this is because if i have a fileID that has not been updated in awhil
e
> then i do not want to waste time reupdating these records.
> 2. get the latest records for the FileID's found in step one. I would have
> to use a subquery to get the latest FileID for each found in step 1.
> 3. update records that to not equal the records found in step 2 where acti
ve
> = 1.
>
> Wanted to know ppls thoughs on my approch and if there is a better way of
> doing it.
> Thanks
>
>|||On Wed, 24 May 2006 17:57:01 -0700, Alejandro Mesa
<AlejandroMesa@.discussions.microsoft.com> wrote:

>Try,
>update t1
>set active = 0
>where exists (
>select *
>from t1 as t2
>where t2.FileID = t1.FileID and t2.DateCreated > t1.DateCreated
> )
>go
That answer does not address the requirement to sequence on FileType
DESC before DateCreated.
Roy Harvey
Beacon Falls, CT|||How can a file be revised before it is created? Is this valid data, or a
typo? Are you concerned with the latest date for each FileID being the
active one, or do you really want to sort alphabetically descending on
FileType?
"John Smith" <zzaro@.excite.com> wrote in message
news:uo$j5G4fGHA.4784@.TK2MSFTNGP03.phx.gbl...
> Hello
> Here is table
> FileID FileType DateCreated Active
> 1 Revised 1-Jan-06 1
> 1 Revised 2-Jan-06 1
> 1 Created 3-Jan-06 1
> 2 Created 4-Jan-06 1
> 2 Revised 5-Jan-06 1
> 3 Created 6-Jan-06 1
> 3 Revised 7-Jan-06 1
> 3 Revised 8-Jan-06 1
> 3 Revised 9-Jan-06 1
>
> and i want to set old records Active column 0. An old record is defined as
> sorting by FileID ASC, FileType DSC, DateCreated DSC. Any record beside
the
> first one is old. I.e.
> FileID FileType DateCreated Active
> 1 Revised 2-Jan-06 1
> 1 Revised 1-Jan-06 0
> 1 Created 3-Jan-06 0
> 2 Revised 5-Jan-06 1
> 2 Created 4-Jan-06 0
> 3 Revised 9-Jan-06 1
> 3 Revised 8-Jan-06 0
> 3 Revised 7-Jan-06 0
> 3 Created 6-Jan-06 0
>
> now this table will keep growing and growing so here is my idea.
> 1. Select FileID's that have a active count > 1 where active = 1. The
reason
> i do this is because if i have a fileID that has not been updated in
awhile
> then i do not want to waste time reupdating these records.
> 2. get the latest records for the FileID's found in step one. I would have
> to use a subquery to get the latest FileID for each found in step 1.
> 3. update records that to not equal the records found in step 2 where
active
> = 1.
>
> Wanted to know ppls thoughs on my approch and if there is a better way of
> doing it.
> Thanks
>|||update t1
set active = 0
where active = 1 --just to avoid rows that do not need updating
and exists
(
select *
from t1 as t2
where t2.FileID = t1.FileID
and
(
(
t2.DateCreated > t1.DateCreated
and t2.FileType = t1.FileType
)
or
t2.FileType < t1.FileType
)
)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:n31a7259n1ndr6ounml4jbmqs5hkgtbadj@.
4ax.com...
> On Wed, 24 May 2006 17:57:01 -0700, Alejandro Mesa
> <AlejandroMesa@.discussions.microsoft.com> wrote:
>
> That answer does not address the requirement to sequence on FileType
> DESC before DateCreated.
>
as
the
> Roy Harvey
> Beacon Falls, CT|||Well there are two ppl that run a program to create a "revised" file or a
"created" file.
the person that creates a "created" file does not work on the wend and
cannot run the "created" file progarm. but the person that runs the revised
can run it 24/7.
this is why i sort by file type. because if i have a "revised" file i dont
care for the "created" file
so yes i need to sort by file type.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eYlpsOAgGHA.4940@.TK2MSFTNGP05.phx.gbl...
> How can a file be revised before it is created? Is this valid data, or a
> typo? Are you concerned with the latest date for each FileID being the
> active one, or do you really want to sort alphabetically descending on
> FileType?
> "John Smith" <zzaro@.excite.com> wrote in message
> news:uo$j5G4fGHA.4784@.TK2MSFTNGP03.phx.gbl...
> the
> reason
> awhile
> active
>|||I want to only deal with fileID that have count(active) > 1 where active = 1
is there a way to do this without a having?
the query you gave me will look at every fileID where its = 1. Every fileID
will always have one record set to one. I onley want to deal with fileID
that have more then one record set to active.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ODjrAVAgGHA.4940@.TK2MSFTNGP05.phx.gbl...
> update t1
> set active = 0
> where active = 1 --just to avoid rows that do not need updating
> and exists
> (
> select *
> from t1 as t2
> where t2.FileID = t1.FileID
> and
> (
> (
> t2.DateCreated > t1.DateCreated
> and t2.FileType = t1.FileType
> )
> or
> t2.FileType < t1.FileType
> )
> )
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:n31a7259n1ndr6ounml4jbmqs5hkgtbadj@.
4ax.com...
> as
> the
>|||You have to look at every fileId to determine if it has a count > 1, my
approach attempts identifies the "Active Row" and update all the other
records by exclusion. In any case, you have to look at the rows with
count=1 in order to exclude them. You can't know how many rows you have
without looking at those rows first.
That said, adding another criteria to the main select may affect
performance. You would have to try it both ways to see which is faster. I
think it is simply redundant, and expect it to hurt performance, but it is
worth a try.
and fileID in (select t3.fileID
from t1 as t3
group by t3.fileID
having count(t3.fileID) >1)
or:
and exists (select t3.fileID from t1 as t3
where t3.fileID = t1.fileID
group by t3.fileID
having count(t3.fileID) >1)
or:
and not exists (select t3.fileID from t1 as t3
where t3.fileID = t1.fileID
group by t3.fileID
having count(t3.fileID) =1)
"John Smith" <zzaro@.excite.com> wrote in message
news:unuhs0AgGHA.1456@.TK2MSFTNGP04.phx.gbl...
> I want to only deal with fileID that have count(active) > 1 where active =
1
> is there a way to do this without a having?
> the query you gave me will look at every fileID where its = 1. Every
fileID
> will always have one record set to one. I onley want to deal with fileID
> that have more then one record set to active.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:ODjrAVAgGHA.4940@.TK2MSFTNGP05.phx.gbl...
>