Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Unanswered: I need help from a real MYSQL Guru

    Hello,

    I need this real bad,

    I need to be able to select column2 from a table where column3 is > 1 and only true 3 times consecutively based on date_column from last 3 days from today and most importantly if column2 happen to also includes column3 value 0 or 1 then the select statement must return none for this record.

    Example

    2002/07/31 test 2
    2002/08/01 test 3
    2002/08/01 test 0
    2002/08/02 test 5

    so in this case the select statement will not return anything since record number 3 had value 0 although same day >1 value occured also.

    But will return "test" in this case

    2002/07/31 test 2
    2002/08/01 test 3
    2002/08/01 test 7
    2002/08/02 test 5


    Sorry, Life is complicated sometimes...

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    I think your going to have to use an agregate function to do the counting if you want this to run in one query ....

    Try something like this ..... confused:

    select column2
    from <TABLE>
    where column3 <> 0
    and column3 <> 1
    group by column2
    having count(*) > 3

  3. #3
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    If you create a temporary table first, containing nothing but the wrong values of column3 (0,1).

    select column2,column3 into temp temp1
    from table
    where column3=1 or column3=0

    then you can start building your main qry:

    select column2
    from table
    where column_date > today-3
    and column2 not in (select column2 from temp1)
    group by column2
    having count(column2) >2
    rws

Posting Permissions

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