Jump to content


  • Posts

  • Joined

  • Last visited

Profile Information

  • Gender
  • Location
    Hyderabad, India

Illusion's Achievements


Newbie (1/5)



  1. Correct!!! My bad. Time to refresh my SQL skills
  2. check this http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour and you don't need use date_format in condition date_format(tblOM.CreatedOn,'%Y-%m-%d')=curdate()) ;
  3. This results duplicates. we need to exclude the records which are returned from first query by adding where clause in second query in the union .i.e " where t.date > '2012-05-03' . I still feel like there can be more optimal way to do this by making union as subquery.
  4. You should use alias names as it is - in this case it doesn't magically know you are dealing with dates 11262012 is not same as 20121126
  5. You can run mysqldump from php script if you have dedicated server for your site or privileges to run the invoke commands (which will not be a case with shared hosting).
  6. from mysql documentation in that case the id range might be mysql_insert_id() to mysql_insert_id() + number of effected rows -1.
  7. First of all the query syntax you have mentioned in your post is not correct. Changing extension form csv to sql will not have any effect , it will be still written as a flat file. If you want to dump table as a sql file you need use mysqldump.
  8. You figured it out or you still need help ?
  9. I would suggest you to have separate query to check whether last home game is a tie or not ... if it is a tie consider that current winning streak is 0 else take the value from the first query output select a.home, if(fth=fta,1,0 ) as tie from engp a inner join (select home,MAX(`date`) as lastdate from engp group by home) b on a.`date`=b.lastdate and a.home=b.home or select home, if(fth=fta,1,0 ) as tie from engp where `date`=(select `date` from engp order by date desc limit 1) if you want to combine , you could combine both queries as well.
  10. Hmm, this is a my solution... set @count:=0,@home:=''; select b.home, MAX(b.flag2) as longestsequence from (Select home,if( @home!=home,@count:=0,@count) as flag1,@home:=home, ( case when fth > fta then @count:=@count+1 else @count:=0 end) as flag2 from (select home,away,fth,fta from engp where season = 'Premier League 11/12' order by home,`date`) a)b group by home order by MAX(b.flag2) desc ; regarding your other question Arsenal last home game is 8.4.2012 00:00:00 Arsenal Man City 1 0 and its not tied. how the current winning streak is calculated actually ?
  11. I think I got it wrong.... Can anybody explain me how a winning streak is calculated? I am not into these sports
  12. If I understand correctly... you want teams order by number of home wins they have in the current season Select home, sum( case when fth > fta then 1 else 0 end) as homewinmatchcount from engp where league = 'Premier League 11/12' group by home order by homewinmatchcount desc
  13. Unfortunately not. A good practice may be inserting the record with a different version by having version column. if you are updating all the columns except id then actually you want to do a insert(assuming there are constraints in doing so ) and delete the old record.
  14. Check this.. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_month
  15. If I am not wrong ...I don't think you can combine any of these queries as none of queries output is subset of any other output. If queries are longer time find a way to optimize them.
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.