1

I have a table tbl_usi in mysql with records as below:


present_date    usi_value    deal_count    
----------------------------------------------------------
2015-10-13      b1              c1           
2015-10-12      b2              c2             
2015-10-11      b3              c3

I want to write a query that will do this using present_date field to select the present date and the date before it and display them together:

present_date    usi_value    deal_count    previous_date   previous_usi_value   previous_deal_count       
----------------------------------------------------------
2015-10-13      b1              c1         2015-10-12      b2                   c2         
2015-10-12      b2              c2         2015-10-11      b3                   c3          
2015-10-11      b3              c3         2015-10-10      b4                   c4

How do I achieve this. Thanks

Gyne
  • 45
  • 7
  • Similar question: https://stackoverflow.com/questions/11509407/add-multiple-rows-in-just-one-row-from-a-single-table – user5226582 Oct 14 '15 at 12:42

2 Answers2

2

Select everything from your table, then join it to itself, making sure the 2 joined tables are given different names so you can distinguish them (I used 'a' and 'b' here). The join offsets the dates by 1 day. Then you can select the fields you want from the joined table.

select 
  a.present_date, 
  a.usi_value, 
  a.deal_count, 
  b.present_date as previous_present_date, 
  b.usi_value as previous_usi_value, 
  b.deal_count as previous_deal_count 
from
  tbl_usi as a 
  left join tbl_usi as b
    on b.present_date = a.present_date - interval 1 day;

If you didn't already have one before, you will now want an index for the present_date column too BTW.

Michael
  • 3,639
  • 14
  • 29
  • Thanks a lot Michael. Your solution retrieves everything in a single row as expected but I now realised that null values are retrieved wherever the previous date is a weekend or public holiday of which no data will be in the table for that date. Is it possible for interval date or any other solution to retrieve the previous date by just off-setting the present_date by 1 row. This way there will be no null rows. I can then order by present_date. Yes I already have a unique key index on present_date. Thanks in Advance – Gyne Oct 14 '15 at 17:25
  • Thanks once again @Michael but I have resolved it now. I created an Auto increment ID field and then used it in the join by off-setting the previous_date by value 1. Here is the query I used. _SELECT c.present_date PresentDate, c.usi current_usi, c.deals current_deals, c.volume current_volume, c.value current_value, c.capitalisation current_capitalisation,p.present_date previous_date, p.usi previous_usi, p.deals previous_deals, p.volume previous_volume, p.value previous_value, p.capitalisation previous_capitalisation FROM usi c LEFT JOIN usi p ON p.ID = c.ID-1 ORDER BY c.ID DESC_ – Gyne Oct 14 '15 at 17:56
  • Yep fair enough, your question didn't state the data was consecutive, and my answer made an assumption. Although you have a solution now, I have posted another answer, for the community benefit, that answers your question, working by date rather than ID... The ID based solution I'm sure works fine for you, and the subquery solution is going to be slower, but of course using ID like this might cause problems later on - it too assumes a uniform consecutive progression of IDs... – Michael Oct 14 '15 at 18:49
0

Alternative, which works when there are date gaps.

select
  a.present_date, 
  a.usi_value, 
  a.deal_count, 
  b.present_date as previous_present_date, 
  b.usi_value as previous_usi_value, 
  b.deal_count as previous_deal_count 
from
  tbl_usi as a 
  join tbl_usi as b
where
  b.present_date = (select max(present_date) from tbl_usi where present_date < a.present_date);

As with previous solution the same table is joined twice, but this time the previous row is found by way of a subquery.

Michael
  • 3,639
  • 14
  • 29
  • This works great @Michael . I actually tried it before but got stock at the WHERE clause. Thanks for this _(where present_date < a.present_date);_ – Gyne Oct 14 '15 at 20:04