Nested select ...any help ?

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

  1. Alfaking

    Alfaking Guest

    Hi,

    I'm trying this
    "select count(*) as Column1,
    (select count(*) from Table1 where Table1.Cond = 'X'
    and Table1.column3=alias1.column3 ) as Column2,
    Column3
    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
    sp1.

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

    this should be used in a vb.net application.

    any help ?
     
    Alfaking, Jul 15, 2009
    #1
    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
    http://www.moving2u.pt - R&D Manager
    http://msmvps.com/AlbertoSilva - 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.

    http://www.eset.com
     
    Alberto Silva, MVP, Jul 16, 2009
    #2
    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.