Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: Getting a column out of a query you defined within the SQL expression

    Hello All,

    I have a query as such:
    Code:
    SELECT Data.Model, Max(Data.Date) AS MaxOfDate
    FROM Data
    what I want to do is use all of the Dates in the column "MaxOfDates" in a WHERE clause. I can do this by making the query above real and saving this information in a table but I would rather not have this table and embed it directly into the query I really want.

    Something like...
    Code:
    SELECT Data.Model, Date.Data FROM Data WHERE Date.Date In(*this is where I want the dates*)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are multiple options. What version of SQL Server are you using?

    One option that probably isn't the most efficient but works on all versions is:
    Code:
    SELECT Data.Model, Data.Date
       FROM Data
       WHERE Data.Date = (SELECT Max(z.Date)
          FROM Data AS z)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    There are multiple options. What version of SQL Server are you using?

    One option that probably isn't the most efficient but works on all versions is:
    Code:
    SELECT Data.Model, Data.Date
       FROM Data
       WHERE Data.Date = (SELECT Max(z.Date)
          FROM Data AS z)
    -PatP
    ahh... good question.... im not
    i am actually creating a query in microsoft access 2010 but I dont think I can do it with just the query options, thats why i need the SQL.
    a few questions... 1. what is "z" are you saying that "Data is z" in that last line?

    secondly... there are multiple models which have multiple latest dates, does this still work for that?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which Microsoft Access database engine are you using? SQL Server is an option, Microsoft Jet is another, and there are a few thousand "also ran" databases that are valid too.

    The use of the Z label allows the data table to be accessed twice in the same query. Once known as data and a second time known as z. In SQL Server and most versions of Microsoft Jet, you can extend the query to accomodate multiple models using syntax like:
    Code:
    SELECT Data.Model, Data.Date
       FROM Data
       WHERE Data.Date = (SELECT Max(z.Date)
          FROM Data AS z
          WHERE  z.Model = Data.Model)
    With my luck, you'll be running a Jet version that doesn't support this syntax, but if not you can upgrade...

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2012
    Posts
    63
    the database was created in accessdb 2010 afaik but it may have initially been 2007

    2007 - ACE12
    2010 - ACE14

    again, I am not extremely comfortable with all the different database types that exist therefore I often mix up what databases can and cant do certain things...

    another thing I saw was from a Microsoft SQL server that used similar notation (where a "variable" or "label" was used" but instead it was used as a temporary table or something? Is this also another possibility? (when I am at work I will post what I saw)

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've moved this message thread to the Microsoft Access forum. One of the folks here will probably have a better Access specific answer for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    run a query which identifies you maxdates....
    then join that query to another one on the max dates

    If you don't know what version of the Access data engine you are using then that almost certainly means you are using JET. you can find it using:- How to find the version of Jet installed?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •