Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

query help - retrieve only those with highest value

Discussion in 'Information Technology' started by access user, Jul 28, 2006.

  1. access user

    access user
    Expand Collapse
    Guest

    I have a query and I wish to restrict the returned values to only those
    where, for a particular field, the value is the highest.

    For example, a property can have had a number of rent agreements but, in the
    following example, I don't want ALL the records, just the one where VarNo is
    the highest

    PropertyNo TenantNo VarNo
    1 1 1
    1 1 2
    1 2 3

    I *only* want the last line not all three lines.

    TIA
    James
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. access user

    access user
    Expand Collapse
    Guest

    Also, just to clarify this is an *additional* constraint to one I already
    have in the query. That is, I want only those results where *both* conditions
    are met.
    Firstly, where ContractEndDate>=Date()
    Secondly, where (having met the above condition), VarNo is the highest

    TIA

    "access user" wrote:

    > I have a query and I wish to restrict the returned values to only those
    > where, for a particular field, the value is the highest.
    >
    > For example, a property can have had a number of rent agreements but, in the
    > following example, I don't want ALL the records, just the one where VarNo is
    > the highest
    >
    > PropertyNo TenantNo VarNo
    > 1 1 1
    > 1 1 2
    > 1 2 3
    >
    > I *only* want the last line not all three lines.
    >
    > TIA
    > James
     
  4. MNewsGroup

    MNewsGroup
    Expand Collapse
    Guest

    123456
    "access user" <accessuser@discussions.microsoft.com> wrote in message
    news:FBCDD485-AAFA-4E68-9E52-11919E664093@microsoft.com...
    > Also, just to clarify this is an *additional* constraint to one I already
    > have in the query. That is, I want only those results where *both*
    > conditions
    > are met.
    > Firstly, where ContractEndDate>=Date()
    > Secondly, where (having met the above condition), VarNo is the highest
    >
    > TIA
    >
    > "access user" wrote:
    >
    >> I have a query and I wish to restrict the returned values to only those
    >> where, for a particular field, the value is the highest.
    >>
    >> For example, a property can have had a number of rent agreements but, in
    >> the
    >> following example, I don't want ALL the records, just the one where VarNo
    >> is
    >> the highest
    >>
    >> PropertyNo TenantNo VarNo
    >> 1 1 1
    >> 1 1 2
    >> 1 2 3
    >>
    >> I *only* want the last line not all three lines.
    >>
    >> TIA
    >> James
     
  5. Michael H

    Michael H
    Expand Collapse
    Guest

    Hi.

    Assuming the name of your table is "TableName", try this:

    SELECT TableName.PropertyNo, TableName.TenantNo, TableName.VarNo
    FROM TableName
    WHERE (TableName.VarNo)=(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE
    (TableName.ContractEndDate)>=Date());

    -Michael


    "access user" wrote:

    > Also, just to clarify this is an *additional* constraint to one I already
    > have in the query. That is, I want only those results where *both* conditions
    > are met.
    > Firstly, where ContractEndDate>=Date()
    > Secondly, where (having met the above condition), VarNo is the highest
    >
    > TIA
    >
    > "access user" wrote:
    >
    > > I have a query and I wish to restrict the returned values to only those
    > > where, for a particular field, the value is the highest.
    > >
    > > For example, a property can have had a number of rent agreements but, in the
    > > following example, I don't want ALL the records, just the one where VarNo is
    > > the highest
    > >
    > > PropertyNo TenantNo VarNo
    > > 1 1 1
    > > 1 1 2
    > > 1 2 3
    > >
    > > I *only* want the last line not all three lines.
    > >
    > > TIA
    > > James
     
  6. Gina

    Gina
    Expand Collapse
    Guest

    In your query you could try putting VarNo field in Descending order and then
    right click - properties and where it says 'All' type in '1'. This will
    return the top value (one record) in the VarNo field.
    --
    Gina


    "access user" wrote:

    > I have a query and I wish to restrict the returned values to only those
    > where, for a particular field, the value is the highest.
    >
    > For example, a property can have had a number of rent agreements but, in the
    > following example, I don't want ALL the records, just the one where VarNo is
    > the highest
    >
    > PropertyNo TenantNo VarNo
    > 1 1 1
    > 1 1 2
    > 1 2 3
    >
    > I *only* want the last line not all three lines.
    >
    > TIA
    > James
     
  7. access user

    access user
    Expand Collapse
    Guest

    Thanks - not sure where to put the 1. Bringing up field properties does not
    give me any such option.
    James

    "Gina" wrote:

    > In your query you could try putting VarNo field in Descending order and then
    > right click - properties and where it says 'All' type in '1'. This will
    > return the top value (one record) in the VarNo field.
    > --
    > Gina
    >
    >
    > "access user" wrote:
    >
    > > I have a query and I wish to restrict the returned values to only those
    > > where, for a particular field, the value is the highest.
    > >
    > > For example, a property can have had a number of rent agreements but, in the
    > > following example, I don't want ALL the records, just the one where VarNo is
    > > the highest
    > >
    > > PropertyNo TenantNo VarNo
    > > 1 1 1
    > > 1 1 2
    > > 1 2 3
    > >
    > > I *only* want the last line not all three lines.
    > >
    > > TIA
    > > James
     
  8. access user

    access user
    Expand Collapse
    Guest

    Thanks - I get a syntax error on the following bit:

    =(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE

    missing ) , ] etc...

    James


    "Michael H" wrote:

    > Hi.
    >
    > Assuming the name of your table is "TableName", try this:
    >
    > SELECT TableName.PropertyNo, TableName.TenantNo, TableName.VarNo
    > FROM TableName
    > WHERE (TableName.VarNo)=(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE
    > (TableName.ContractEndDate)>=Date());
    >
    > -Michael
    >
    >
    > "access user" wrote:
    >
    > > Also, just to clarify this is an *additional* constraint to one I already
    > > have in the query. That is, I want only those results where *both* conditions
    > > are met.
    > > Firstly, where ContractEndDate>=Date()
    > > Secondly, where (having met the above condition), VarNo is the highest
    > >
    > > TIA
    > >
    > > "access user" wrote:
    > >
    > > > I have a query and I wish to restrict the returned values to only those
    > > > where, for a particular field, the value is the highest.
    > > >
    > > > For example, a property can have had a number of rent agreements but, in the
    > > > following example, I don't want ALL the records, just the one where VarNo is
    > > > the highest
    > > >
    > > > PropertyNo TenantNo VarNo
    > > > 1 1 1
    > > > 1 1 2
    > > > 1 2 3
    > > >
    > > > I *only* want the last line not all three lines.
    > > >
    > > > TIA
    > > > James
     
  9. Michael H

    Michael H
    Expand Collapse
    Guest

    Did you paste the entire bit of SQL I posted? If your SQL ends at the word
    "WHERE", then you did not. Please ensure thay you copy and paste everything
    from the first SELECT statement to the semicolon at the end. I have tested
    it and it works just as you had specified it should.

    If you have done the above and are still having problems, please post the
    exact SQL you are using, as something may have gotten changed in the
    transition.

    -Michael


    "access user" wrote:

    > Thanks - I get a syntax error on the following bit:
    >
    > =(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE
    >
    > missing ) , ] etc...
    >
    > James
    >
    >
    > "Michael H" wrote:
    >
    > > Hi.
    > >
    > > Assuming the name of your table is "TableName", try this:
    > >
    > > SELECT TableName.PropertyNo, TableName.TenantNo, TableName.VarNo
    > > FROM TableName
    > > WHERE (TableName.VarNo)=(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE
    > > (TableName.ContractEndDate)>=Date());
    > >
    > > -Michael
    > >
    > >
    > > "access user" wrote:
    > >
    > > > Also, just to clarify this is an *additional* constraint to one I already
    > > > have in the query. That is, I want only those results where *both* conditions
    > > > are met.
    > > > Firstly, where ContractEndDate>=Date()
    > > > Secondly, where (having met the above condition), VarNo is the highest
    > > >
    > > > TIA
    > > >
    > > > "access user" wrote:
    > > >
    > > > > I have a query and I wish to restrict the returned values to only those
    > > > > where, for a particular field, the value is the highest.
    > > > >
    > > > > For example, a property can have had a number of rent agreements but, in the
    > > > > following example, I don't want ALL the records, just the one where VarNo is
    > > > > the highest
    > > > >
    > > > > PropertyNo TenantNo VarNo
    > > > > 1 1 1
    > > > > 1 1 2
    > > > > 1 2 3
    > > > >
    > > > > I *only* want the last line not all three lines.
    > > > >
    > > > > TIA
    > > > > James
     

Share This Page