Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Need Help With A Query Please

    Hi,

    I'm basically trying to join a table to a query. The problem is, I'd like to do it all in one statement. A simple subquery doesn't work because I need to join two fields to make this work. In MS-Access, this is what it looks like now:

    Query:
    SELECT TESTTABLE.SEQ, Max(TESTTABLE.DATE) AS MaxOfDATE
    FROM TESTTABLE
    GROUP BY TESTTABLE.SEQ;

    Query Joined to Table:
    SELECT TESTTABLE.*
    FROM TESTTABLE INNER JOIN MAXQUERY ON ([TESTTABLE].[DATE]=[MAXQUERY].[MaxOfDATE]) AND ([TESTTABLE].[SEQ]=[MAXQUERY].[SEQ]);

    Here is the source table:
    ID SEQ DATE TEXTIEM
    1 1 12/1/2003 Is this a question?
    2 1 11/1/2003 Was this a question?
    3 2 12/1/2003 Are you ugly?
    4 2 11/1/2003 Is your mother ugly?

    Here is the desired return:
    ID SEQ DATE TEXTIEM
    1 1 12/1/2003 Is this a question?
    3 2 12/1/2003 Are you ugly?

    Help would be greatly appreciated (in peanuts, of course)

    Tom

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select t.*
    from testtable t
    where date_tf = (select max(date_tf) from testtable where seq = t.seq);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    Thanks, but that I need to reference more than one column. Needs to be a right join to a query. Can I alias a query in MS SQL?

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    To reference a set/query,

    select v1.seq, v2.columns
    from
    (select seq, max(date) maxDate
    from table
    group by seq) V1
    INNER JOIN
    (set) v2 ON
    condition
    Last edited by r123456; 01-10-04 at 04:20.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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