sql subselect - Sqlite query - Where In (subquery) -

goal: i'm working on windows phone app allows save birthdays. windows phone doesn't allow save more 50 reminders, i'm trying is:

i want list of next 50 birthdays-days. list has distinct, because if example next 50 birthdays fall on same day, don't want create 50 reminders 1 day, want create 1 reminder 50 birthdays.

this part of query create next birthday (if birthday passed, create birthday next year)

case when strftime('%m-%d', bday) < strftime('%m-%d', 'now') strftime('2015-%m-%d', bday) else strftime('2014-%m-%d', bday) end bday 

so full query, doesn't work ...:

select id, type, details,                  case when strftime('%m-%d', bday) < strftime('%m-%d', 'now')                 strftime('2015-%m-%d', bday)                 else strftime('2014-%m-%d', bday)                 end bday birthday  bday in       (select distinct           case when strftime('%m-%d', bday) < strftime('%m-%d', 'now')          strftime('2015-%m-%d', bday)          else strftime('2014-%m-%d', bday)          end nextbday        birthday        order nextbday        limit 50) order bday 

if run subquery seperatly, give me results i'm looking for:

2015-01-01 2015-01-02 

if run main query without get:

id  type       details            bday 1   11111      admin@sqlfiddle.com    2015-01-01 3   33333      admin@sqlfiddle.com    2015-01-01 2   22222      @sqlfiddle             2015-01-02 4   44444      @sqlfiddle             2015-01-02 

but combined, no results ...

i made sqlfiddle: http://sqlfiddle.com/#!5/58849/6/0

i hope some1 can me this! in advance!

you're trying rename field in as clause. bday field in where clause refers bday in table definition, not 1 in select clause.

to fix issue, change instead (gives 2 rows of results on fiddle):

select id, type, details,   case when strftime('%m-%d', bday) < strftime('%m-%d', 'now')  strftime('2015-%m-%d', bday)  else strftime('2014-%m-%d', bday)  end fbday birthday  fbday in  (select distinct       case when strftime('%m-%d', bday) < strftime('%m-%d', 'now')      strftime('2015-%m-%d', bday)      else strftime('2014-%m-%d', bday)      end nextbday   birthday   order nextbday   limit 1) order bday