Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: problem with min(date)

    Hi everyone, here is the offending script:

    Code:
    select distinct person, min(pdate) as min_date from db
    where ptype like 'A1%'
    or (ptype like 'B1%'
    and (pdate between '2000-01-01' and '2001-01-01'))
    group by person
    The problem

    The min_date retreived also include those outisde of the pdates I have specified. However when I take out one of the ptypes so I have:

    Code:
    select distinct person, min(pdate) as min_date from db
    where ptype like 'A1%'
    and (pdate between '2000-01-01' and '2001-01-01')
    group by person
    then this problem goes away. Why is it that introducing a second ptype is retuning instances outside of the pdates i have specified?

    Thanks.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In your first query, rows are qualified if ptype is A1% ... no filter for pdate.
    Code:
    (ptype like 'A1%'
    or  ptype like 'B1%') 
    and (pdate between '2000-01-01' and '2001-01-01')
    this will choose rows with ptype starting with A1 or B1 and within the pdate range.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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