Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4991

Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4993
Question 81: More solutions for Q80? – MySQL Question of the Day

Skip to content

Question 81: More solutions for Q80?

10. July 2010
By plogi in mysql questions

1 Comment

Hi!
It is really important to find as many different solutions as possible for a problem. The reason is, that sometimes a certain solution won’t work depending on the situation. So you need to have alternatives.
For question 80 I offered two possibilities, one with user-variables and one with a subquery. Are there more?

[Just for fun]

Tags: , ,

Comment Feed

One Response

  1. plogi — 10. July 2010 @ 19:09:04 —


    Another solution for Q80 is using a helper table and a join.
    The helper table is created with the correct sort order and a surrogate key is added to support the join between the rows.

    drop table if exists log1;
set @a:=0;
create table log1
  select @a:=@a+1 as row_id,v1.*
  from vending_log v1 order by sold_ts;
alter table log1 add primary key(row_id);

select avg(unix_timestamp(v1.sold_ts)-unix_timestamp(v2.sold_ts)) as avg_diff
from log1 v1, log1 v2
where v2.row_id=v1.row_id - 1;

You must be logged in to post a comment.