RE: Compare row data and input data into a new field

>>when it gets to the
last row that has the same id then to place a "1" in a check field. <<

With ref. to your above statement, how will you be defining last row, will
it be on the basis of the maximum number of week_as_int column for respective
id
OR will it be on the basis of maximum date from "weekending" column for
respective id?

If its on the basis of max of week_As_int column your query will look like
as shown in the following example, you can change the same query by selecting
max of weekending column.

--table and sample data

create table t
(ID varchar(50),Week_as_Int int, WeekEnding datetime, chk int)

insert into t (id, week_As_int,weekending)
select 'ASome',523,'2005/06/5' union all
select 'ASome',548,'2005/11/27' union all
select 'ASome',549,'2005/12/4' union all
select 'ASome',604,'2005/01/22' union all
select 'bSome',700,'2005/06/5' union all
select 'bSome',549,'2005/12/4' union all
select 'bSome',604,'2005/01/22' union all
select 'cSome',700,'2005/06/5' union all
select 'cSome',701,'2005/12/4' union all
select 'cSome',704,'2005/01/22'

--update statement to update the column chk .

update t
set chk = 1
from t a join
(select "id", max(week_as_int) week_as_int
from t
group by "id") b on a.id = b.id and a.week_as_int = b.week_as_int



"Newman Emanouel" wrote:

> Dear All
>
> I have a simple query which I cannot seem to work out and would like some
> help.
>
> I have a small table with data like such
>
> ID Week_as_Int WeekEnding Check
> ASome 523 5/06/2005
> ASome 531 31/07/2005
> ASome 548 27/11/2005
> ASome 549 4/12/2005
> ASome 604 22/01/2006 1
>
> what I need to do is to create a new column called check and put a "1" check
> filed in the last one of the selection. In the example above it would be the
> row with "604" in it but it is not always going to be "604" it could be
> anything. So what I need to do is to look at each row and when it gets to the
> last row that has the same id then to place a "1" in a check field.
>
> The answer is easy using excel but I would like to take that intervension
> out an incorporate it in a SQL script as part of the automation
>
> Thanking you in advance
>
> Regards
>
> Newman
>
>
.

Relevant Pages

  • Re: ? union and intersection of two sets of free vectors
    ... To get a basis for U union V, first reduce the set of u_i's to a basis; ... To get a basis for U intersect V, take the set of all v_i's that belong ... Similarly, the span of is a subspace, V, of R^N. ...
    (sci.math)
  • Re: ? union and intersection of two sets of free vectors
    ... U union V, put all the u_i's and v_i's in a single matrix and reduce. ... column belongs to U. So to finally get a basis for U intersect V, ... one check if v_i is not in the span of B? ...
    (sci.math)
  • Re: ? union and intersection of two sets of free vectors
    ... U union V by adding the v_i's for which v_i is not in the span of ... To create basis for U, one way is to put all vectors beloning ... to U in a matrix and then do the column reduction to get those ... one check if v_i is not in the span of B? ...
    (sci.math)
  • Re: a countable basis of topological space (X, T) imply that their open sets satisfify the basis cri
    ... > Assumme is a countable basis for T. ... That is why do the elements of T satisfy the basis ... so A is a union of sets from the base ...
    (sci.math)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)