2

I have a table play day with following columns date_played, winner, loser with following values,

(Jun-03-14, USA, China)
(Jun-05-14, USA, Russia)
(Jun-06-14, France, Germany)
.
.
.
.
(Jun-09-14, USA, Russia)

I need to obtain all instances where USA has won exactly 3 rows in a sequence.

I tried with the following query.

Select 
    date, winner, loser, 
    RANK() OVER (PARTITION BY winner ORDER BY date rows 2 preceding) as rank 
from playday;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
paddu
  • 693
  • 1
  • 7
  • 19
  • 1
    Answer would depend on SQL engine, and also you should define more clearly what does it mean "in sequence" - what exactly defines a sequence? You don't seem to have any unique field that can define distinctive order – mvp Jul 01 '14 at 06:36
  • Sequence is the date where USA was a winner. For SQL engine, it can be postgres or mySQL. – paddu Jul 01 '14 at 14:23
  • This is still not enough information - what if there are 2 or more plays at the same day? Note that Postgres and MySQL engines have very different capabilities, please say which one. Postgres supports windowing functions, while MySQL doesn't – mvp Jul 01 '14 at 16:36
  • Lets assume Postgres. If there is tie, we can use dense_rank. – paddu Jul 01 '14 at 23:30

3 Answers3

1

first you need to find out when was the last time they lost. second count the number of wins, greater than (>) the date of the last time they lost. third return all rows greater than last loss, if count > 3.

sorry, don't have an SQL parser in front of me to put it in code properly.

Set @team_name = "USA";
select date, winner, loser
from playday
where (select count(*) as wins_since_loss from playday
       where playday.winner = @team_name
       and playday.date >
    (select max(date) as losing_date from playday where playday.loser = @team_name)) = 3
simo.3792
  • 2,102
  • 1
  • 17
  • 29
  • I don't think one to complicate this query the way you did. It can be done with SQL Analytical function. – paddu Jul 01 '14 at 14:28
  • My main point in my solution, is that you needed a sequence and not just a count. Therefore you cannot just "count" the previous 2 winners and then get a third. You also need to identify and exclude results, if the same team appears in the losers column. If you don't need a sequence of 3 wins and simply need to count the 3rd win, then your question is misleading. – simo.3792 Jul 02 '14 at 05:28
1

You can use the following query.

select winner,loser,date,cnt from (select winner, loser, date, date - lag(date,3) over ( order by date) as cnt  from playday) where cnt >=3
Newbie
  • 146
  • 2
  • 12
-1

The query is to pull sequence of rows where USA won 3 time in a row, not less or more (I used date as date1)

select   date1, winner, loser from
(
  select count (*) over (partition by change) as id, date1,winner,loser from 
  (
    select date1,winner,loser,lag_loser, sum(case when loser <> lag_loser and (loser='USA' or lag_loser='USA') then 1 else 0 end) over (order by date1 rows unbounded preceding) as change from 
    (
      select date1, winner,loser, lag(loser) over (order by date1) as lag_loser from
      (
        select date1, winner, loser from playday
        where winner ='USA' or loser = 'USA'
        ORDER BY date1 ASC
      )
    )
  )
)
where winner ='USA' and id =3
Pavlin
  • 5,390
  • 6
  • 38
  • 51
Ravindra Rapaka
  • 9
  • 3