1

So I asked this question a while back and now I've been confronted with a nasty variation.

Say I have this table:

ID  Date        Special 
1   2001-01-11  1      
1   2003-03-03      
1   2002-02-22  1       
2   2001-01-11  1      
2   2002-02-22  1

I need to enumerate these records, both by the date, but also depending on if the record was marked as Special or not.

The ideal output would be as such:

ID  Date        Special  Num 
1   2001-01-11  1        1      
1   2003-03-03      
1   2002-02-22  1        2
2   2001-01-11  1        1
2   2002-02-22  1        1

Here's the table:

CREATE TEMPORARY TABLE temp_table(id INT, dt DATE, Special INT);
INSERT INTO temp_table VALUES
(1, '2001-01-11', 1),
(1, '2003-03-03', NULL),
(1, '2002-02-22', 1),
(2, '2001-01-11', 1),
(2, '2002-02-22', 1);

I would love to be able to modify the answer I got to the aboveformentioned question, but it uses that declarative side of SQL that I'm terrible at. Thanks for looking at this!

Monica Heddneck
  • 2,973
  • 10
  • 55
  • 89
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Sep 30 '17 at 00:36
  • Humm....you think this warrants one? I'll make one you think I should... Come to think of it, that's actually one of the more annoying things about SQL. When I find myself asking questions about R, Python, it's so easy and quick to create a MCVE...but MySQL is just...blech. – Monica Heddneck Sep 30 '17 at 00:40
  • Several things are not clear. How are the rows sorted, if not by date? Why aren't you using standard date format YYYY-MM-DD? Is the Special column NULL or `''` when it's not `'Y'`? How do you want to handle the case when the first row in a group has Special not `'Y'`? – Bill Karwin Sep 30 '17 at 00:45
  • 1
    Tip: `mysqldump mydatabase mytable` gives you a lot of what you need for an MCVE. It outputs the `SHOW CREATE TABLE` for the table(s) involved, as well as the data in convenient `INSERT` statements. You can then trim it down to something you can make into a SQLFiddle. – Bill Karwin Sep 30 '17 at 00:47
  • Got it! Thanks for pointing this out to me. I'll clarify the question. – Monica Heddneck Sep 30 '17 at 00:51
  • I don't understand the order of the results. What's your PRIMARY KEY? – Strawberry Sep 30 '17 at 10:11

1 Answers1

2

I looked at the previous question...the answer seemed like a little much for what you were asking. This is a shorter version.

select @ctr := if(@id = id,@ctr+1,1) ctr
       ,dt date
       ,@id :=id id
       from q43381823
       order by id,dt

if you want to exclude the special all together then this...

select @ctr := if(@id = id,@ctr+1,1) ctr
       ,dt date
       ,@id :=id id
       from q43381823
       where special = 'Y'
       order by id,dt

then if you want to include the special field, this will work...

select @ctr := if(@id = id,@ctr+1,1) ctr
       ,dt
       ,@id :=id id
       ,special
       from q43381823
       where special = 'Y'
union
select '' as ctr, dt , id, special
       from q43381823
       where special <> 'Y'
       order by id,dt

Also, I used the create table from the other question.

CREATE TABLE q43381823(id INT, dt DATE, special varchar(1));
INSERT INTO q43381823 VALUES
(1, '2001-01-11','Y'),
(1, '2003-03-03',''),
(1, '2002-02-22', 'Y'),
(2, '2001-01-11', 'Y'),
(2, '2002-02-22', 'Y');
beautiful.drifter
  • 311
  • 1
  • 7