Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: max works but not min????

    Here is my table:

    teClient......teDate..................teCode
    AFG...........10/14/2003...............100
    AFG...........1/14/2004.................100
    AFG...........10/15/2003...............100
    AFG...........2/20/2004.................100
    AFG...........1/23/2004.................100
    AFG...........11/8/2003.................100
    AFG...........10/14/2002...............200
    AFG...........1/14/2002.................200
    AFG...........10/15/2002...............200
    AFG...........2/20/2002.................200
    AFG...........1/23/2002.................200
    AFG...........11/8/2002.................200

    I am trying to run a query that will show me the highest date for just the 100 codes as well as a code that will show me the lowest date for just the 100 codes.

    I tried the following:

    Max100: Max(IIf([teCode]='100',[teDate],0))
    Min100: Min(IIf([teCode]='100',[teDate],0))

    but it only seems to work for the Max function and returns 0 or the earliest date for any teCode.

    Any ideas?

    Thanks,

    Norm

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What about this:

    SELECT TOP 1 teDate FROM MyTable WHERE ([teCode]='100') ORDER BY teDate ASC;

    - And -

    SELECT TOP 1 teDate FROM MyTable WHERE ([teCode]='100') ORDER BY teDate DESC;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this:
    PHP Code:
    select max(teDate) as maxDate
         
    min(teDate) as minDate
      from yourtable
     where teCode 
    100 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by r937
    try this:
    PHP Code:
    select max(teDate) as maxDate
         
    min(teDate) as minDate
      from yourtable
     where teCode 
    100 
    Unfortunately I will be trying to get dates for each different time code. So I will have a max and min date for both teCode=100 and a different one for teCode=200 and so on (until 900).

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah

    well, you did say "just the 100 codes" (twice)
    PHP Code:
    select teCode
         
    max(teDate) as maxDate
         
    min(teDate) as minDate
      from yourtable
    group
        by teCode 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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