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? Feb 1, 2014 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, 2014 at 12:09
  • 1
    VARCHAT() - French cat-based data types!
    – Philᵀᴹ
    Feb 1, 2014 at 12:13
  • 1
    @Mihai he means WHERE date='2014-01-03' GROUP BY Trd. There is no Trd=1 in that date. Feb 1, 2014 at 12:13
  • There is no B for 3-Jan-14 in your sample.
    – Mihai
    Feb 1, 2014 at 12:42

3 Answers 3


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, 2014 at 8:21
  • I've edited with index suggestions. How many different values of Trd does the distinct subquery return (in average)? Feb 12, 2014 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, 2014 at 9:15
  • Works! Not to mention the query + the indexes amount for quite a quick SQL class.
    – VBOG
    Feb 13, 2014 at 9:51
  • So, how does it perform with the indexes? From 12+ hours, down to? Feb 13, 2014 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 and acknowledge you have read our privacy policy.

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