Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What is the primary key of the table? –  ypercube Feb 1 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 at 12:09
1  
VARCHAT() - French cat-based data types! –  FreshPhilOfSO Feb 1 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 at 12:13
    
There is no B for 3-Jan-14 in your sample. –  Mihai Feb 1 at 12:42
show 7 more comments

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
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 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 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 at 9:15
    
Works! Not to mention the query + the indexes amount for quite a quick SQL class. –  VBOG Feb 13 at 9:51
    
So, how does it perform with the indexes? From 12+ hours, down to? –  ypercube Feb 13 at 9:54
show 1 more comment

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';
share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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