Nested select ...any help ?

Discussion in 'microsoft.public.sqlserver.ce' started by Alfaking, Jul 15, 2009.

  1. Alfaking

    Alfaking Guest


    I'm trying this
    "select count(*) as Column1,
    (select count(*) from Table1 where Table1.Cond = 'X'
    and Table1.column3=alias1.column3 ) as Column2,
    from table1 as alias1 group by Column3 order by column1 Desc"

    It works fine in sql server 2005, but it's not working in sql ce 3.5

    the result is
    column1|column2|column3 --> from 1 table, but with 2 different

    this should be used in a application.

    any help ?
    Alfaking, Jul 15, 2009
    1. Advertisements

  2. Hi,
    Try this way:

    SELECT count(*) as Column1, alias2.recordcount as Column2, column3
    FROM table1 AS alias1 LEFT JOIN
    (SELECT column3, count(*) AS recordcount FROM Table1 GROUP BY column3
    WHERE cond='X' ) AS alias2
    ON alias1.column3 = alias2.column3
    GROUP BY column3
    ORDER BY Column1 Desc

    It may not work at the first try, I have not tested the instruction but this
    syntax is supposed to work. Beware that, at least on Windows Mobile/CE
    devices, it may be more performant to break the instruction in two, getting
    the 2nd count(*) with a prepared ExecuteScalar command while iterating over
    the SELECT results.


    Alberto Silva - R&D Manager - Blog
    Microsoft MVP - Device Application Development

    __________ Information from ESET NOD32 Antivirus, version of virus signature database 4249 (20090716) __________

    The message was checked by ESET NOD32 Antivirus.
    Alberto Silva, MVP, Jul 16, 2009
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.