# How to query for 'First showed up as'

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
VARCHAT() - French cat-based data types! – Phil Feb 1 '14 at 12:13
@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`):

``````SELECT DISTINCT Trd
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:

``````SELECT
'2014-01-03' AS Date,
t.Trd,
t.Code,
t.Date       AS InDate,
t.T_Date
FROM
( SELECT DISTINCT Trd
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,
ti.ID
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
values
('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
join
cte on  test.trd = cte.trd
where test.f_date = '20140103'
and cte.rank_no = 1;

--Results

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
FROM
(SELECT @date:= -1) s,
(SELECT @trd:= -1) c,
(SELECT *
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';
``````
-