Given the following table:

 Date       Trd (INT(10))   Code (VARCHAR(10))  T-Date
 1-Jan-14   1               B                   1-Nov-13
 1-Jan-14   2               B                   3-Oct-13
 1-Jan-14   2               B                   3-Oct-13
 2-Jan-14   1               A                   1-Nov-13
 2-Jan-14   2               A                   3-Oct-13
 2-Jan-14   2               A                   3-Oct-13
 2-Jan-14   3               A                   2-Jan-14
 3-Jan-14   2               A                   3-Oct-13
 3-Jan-14   2               A                   3-Oct-13
 3-Jan-14   3               A                   2-Jan-14
 3-Jan-14   4               C                   3-Jan-14

How do I SELECT, to get the following result:

 Date       Trd (INT(10))   First showed up as Code   In Date       T-Date
 3-Jan-14   2               B                         1-Jan-14      3-Oct-13
 3-Jan-14   3               A                         2-Jan-14      2-Jan-14
 3-Jan-14   4               C                         3-Jan-14      3-Jan-14

So for a given Date (in this case Jan-3rd), GROUP BY Trd and find out with which Code it first showed up, and provide the Date that happened.

I have no idea where to start.

  • What is the primary key of the table? – ypercubeᵀᴹ Feb 1 '14 at 12:01
  • I dont think you need GROUP BY trd since the main criterion is Code.Where is trd 1 if you need GROUP BY trd? – Mihai Feb 1 '14 at 12:09
  • 1
    VARCHAT() - French cat-based data types! – Philᵀᴹ Feb 1 '14 at 12:13
  • 1
    @Mihai he means WHERE date='2014-01-03' GROUP BY Trd. There is no Trd=1 in that date. – ypercubeᵀᴹ Feb 1 '14 at 12:13
  • There is no B for 3-Jan-14 in your sample. – Mihai Feb 1 '14 at 12:42

First, find the Trd values for the specific date (using either GROUP BY or DISTINCT):

FROM tableX
WHERE Date = '2014-01-03' ;

Then you can use the above as the "driving" subquery to get the first appearance of these Trd values. Basically, this type of queries is often called [greatest-n-per-group] (there's even a tag at SO!) There are many ways to be done, depending on how you want ties to be shown - and resolved. The following shows only one result per Trd and resolves ties on Date by using the ID column:

    '2014-01-03' AS Date,
    t.Date       AS InDate,
      FROM tableX
      WHERE Date = '2014-01-03' 
    ) AS dt
  JOIN tableX AS t
    ON  t.ID =
        ( SELECT ti.ID
          FROM tableX AS ti
          WHERE ti.Trd = dt.Trd
          ORDER BY ti.Date,
          LIMIT 1
        ) ;

Two indexes, on (Date, Trd) and on (Trd, Date, ID) would help efficiency.

  • This is a max once a month query so I don't care much about performance. Still, its runs for over 12 hours and never finishes (show processlist tells me it is still running). Didn't want to change anything for this query but think I will have to index Trd to make it work. This is a single-table DB composed of 360 10k lines x 61 cols csv files. Any thought? – VBOG Feb 12 '14 at 8:21
  • I've edited with index suggestions. How many different values of Trd does the distinct subquery return (in average)? – ypercubeᵀᴹ Feb 12 '14 at 8:59
  • 31200 if I run for 2014-01-03, I would guess no more then 60k overall in the database. Will try the indexes... – VBOG Feb 12 '14 at 9:15
  • Works! Not to mention the query + the indexes amount for quite a quick SQL class. – VBOG Feb 13 '14 at 9:51
  • So, how does it perform with the indexes? From 12+ hours, down to? – ypercubeᵀᴹ Feb 13 '14 at 9:54

My solution using SQL Server is as below. I could not test it in mysql as my ubuntu server is down. Also, I am not sure if mysql implements cte and window functions yet. In such case you may have to re-write the query as a stored procedure.

--Table definition

create table test
(f_date date,
trd int,
code varchar(10),
t_date date);

--Populate table

insert into test
 ('1-Jan-14',   1,          'B',                  '1-Nov-13'),
 ('1-Jan-14',   2,          'B',                  '3-Oct-13'),
 ('1-Jan-14',   2,          'B',                  '3-Oct-13'),
 ('2-Jan-14',   1,          'A',                  '1-Nov-13'),
 ('2-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('2-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('2-Jan-14',   3,          'A',                  '2-Jan-14'),
 ('3-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('3-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('3-Jan-14',   3,          'A',                  '2-Jan-14'),
 ('3-Jan-14',   4,          'C',                  '3-Jan-14');

--Quey code

 ; with cte as
 (select RANK()over(partition by trd order by f_date) as rank_no, trd, code, f_date
 from dbo.test) 
 select distinct test.f_date as 'Date', cte.trd as 'Trd', cte.code as 'First showed up as code',
 cte.f_date as 'In Date', test.t_date as 'T-Date'
 from test
 cte on  test.trd = cte.trd
 where test.f_date = '20140103'
 and cte.rank_no = 1;


Date    Trd First showed up as code In Date T-Date
2014-01-03  2   B   2014-01-01  2013-10-03
2014-01-03  3   A   2014-01-02  2014-01-02
2014-01-03  4   C   2014-01-03  2014-01-03

Workable mysql solution is as below.

SELECT Date, Trd, Code as 'First showed up as code',
InDate as 'In Date', TDate as 'T-Date' from (
SELECT s.f_date as 'Date', t.trd as 'Trd', t.code as 'Code',
  t.f_date as 'InDate', s.t_date as 'TDate',
   @date:=CASE WHEN @trd <> t.trd THEN 0 ELSE @date+1 END AS rn,
   @trd:=t.trd AS trdset
  (SELECT @date:= -1) s,
  (SELECT @trd:= -1) c,
   FROM test
   ORDER BY trd, f_date
  ) as t
join test as s
on s.trd = t.trd) as o
where rn = 0
and Date = '20140103';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.