Sviter...

Hoho...

För att räkna antalet vinster använder jag följande kod:

SQL = "SELECT Count(*) FROM Matcher WHERE " & _
"(homeID = " & ID & " AND homeGoals > awayGoals) OR " & _
"(awayID = " & ID & " AND homeGoals < awayGoals)"

Nu vill jag räkna ut flest matcher i rad utan förlust. Jag vet hur jag löser detta i ASP kod, men tänkte om det gick att ordna direkt i SQL satsen.

/ Marcus

Med referens till <http://www.sqlteam.com/item.asp?ItemID=12654> kan vi skriva

select case when
         (homeid = <ID> and homegoals >= awaygoals)
           or
         (awayid = <ID> and awaygoals >= homegoals) then 1 else 0
       end as point
     , (select count(*)
          from matcher
         where <date column> < m.<date column>
           and ((homeid = <ID> and homegoals < awaygoals)
                 or
               (awayid = <ID> and awaygoals < homegoals))) as rg
     , count(*) as streak
  from matcher m
 where (homeid = <ID> and homegoals >= awaygoals)
    or (awayid = <ID> and awaygoals >= homegoals)
group
    by point
     , rg
order
    by streak desc
 limit 1

Det var väl ganska väntat att jag skulle få ett felmeddelande...

SQL = "select case when " & _
"(homeid = " & ID & " and homegoals >= awaygoals) " & _
"or " & _
"(awayid = " & ID & " and awaygoals >= homegoals) then 1 else 0 " & _
"end as point " & _
", (select count(*) " & _
"from matcher " & _
"where matchdate < m.matchdate " & _
"and ((homeid = " & ID & " and homegoals < awaygoals) " & _
"or " & _
"(awayid = 1 and awaygoals < homegoals))) as rg " & _
", count(*) as streak " & _
"from matcher m " & _
"where (homeid = " & ID & " and homegoals >= awaygoals) " & _
"or (awayid = " & ID & " and awaygoals >= homegoals) " & _
"group " & _
"by point " & _
", rg " & _
"order " & _
"by streak desc " & _
"limit 1"

...ger...

Error Type:
(0x80004005)
Unspecified error 
/stat/test.asp, line 38

Line 38 är koden då jag kör SQL-satsen. Den klagar alltså på SQL-satsen... ;-)

Vilket DBMS? Om SQL Server:

select top 1 case when
         (homeid = <ID> and homegoals >= awaygoals)
           or
         (awayid = <ID> and awaygoals >= homegoals) then 1 else 0
       end as point
     , (select count(*)
          from matcher
         where matchdate < m.matchdate
           and ((homeid = <ID> and homegoals < awaygoals)
                 or
               (awayid = <ID> and awaygoals < homegoals))) as rg
     , count(*) as streak
  from matcher m
 where (homeid = <ID> and homegoals >= awaygoals)
    or (awayid = <ID> and awaygoals >= homegoals)
group
    by point
     , rg
order
    by streak desc

Läs gärna @nders guide till vad som bör inkluderas då frågor ställs.

Access... sorry... glömmer alltid av sånt =)

Testa

select top 1
       iif((homeid = <ID> and homegoals >= awaygoals)
             or
           (awayid = <ID> and awaygoals >= homegoals),1,0) as point
     , (select count(*)
          from matcher
         where matchdate < m.matchdate
           and ((homeid = <ID> and homegoals < awaygoals)
                  or
                (awayid = <ID> and awaygoals < homegoals))) as rg
     , count(*) as streak
  from matcher m
 where (homeid = <ID> and homegoals >= awaygoals)
    or (awayid = <ID> and awaygoals >= homegoals)
group
    by point
     , rg
order
    by streak desc

<ID> Skall väl ersättas med " & ID & " ??

SQL = "select top 1 " & _
"       iif((homeid = " & ID & " and homegoals >= awaygoals) " & _
"             or " & _
"           (awayid = " & ID & " and awaygoals >= homegoals),1,0) as point " & _
"     , (select count(*) " & _
"          from matcher " & _
"         where matchdate < m.matchdate " & _
"           and ((homeid = " & ID & " and homegoals < awaygoals) " & _
"                  or " & _
"                (awayid = " & ID & " and awaygoals < homegoals))) as rg " & _
"     , count(*) as streak " & _
"  from matcher m " & _
" where (homeid = " & ID & " and homegoals >= awaygoals) " & _
"    or (awayid = " & ID & " and awaygoals >= homegoals) " & _
"group " & _
"    by point " & _
"     , rg " & _
"order " & _
"    by streak desc"

Gav...

Error Type:
Microsoft JET Database Engine (0x80040E21)
You tried to execute a query that does not include the specified expression 'IIf(homeid=334 And homegoals>=awaygoals Or awayid=334 And awaygoals>=homegoals,1,0)' as part of an aggregate function.