Select all rows with highest value of group


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Select all rows with highest value of group

  1. #1
    Roy Guest

    Select all rows with highest value of group


    (Pulling my hair out!!)
    I have a table of four columns:

    PROJ - a project name
    TASK - a task name
    EFFDATE - Effective date of a Due Date
    DUEDATE - the acutual due date.

    There can be multiple rows of PROJ/TASK combinations.

    I'd like to select what the current schedule would be: All the rows except
    rows where there is another identical PROJ/TASK combination and the EFFDATE
    is not the highest for that combination.

    The "select" logic would be something like:

    SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK combinations
    where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK combination.

    Could someone show me a valid SELECT statement for this? It seems simple
    but I've tried many unsucessful satements!

    Thanks,
    Roy




  2. #2
    Qbert Guest

    Re: Select all rows with highest value of group


    Roy,
    You could do

    SELECT proj, task, Max(effdate), duedate
    FROM table
    WHERE
    GROUP BY Proj, Task, Duedate

    "Roy" <roy.antetomaso@compaq.com> wrote:
    >
    >(Pulling my hair out!!)
    >I have a table of four columns:
    >
    >PROJ - a project name
    >TASK - a task name
    >EFFDATE - Effective date of a Due Date
    >DUEDATE - the acutual due date.
    >
    >There can be multiple rows of PROJ/TASK combinations.
    >
    >I'd like to select what the current schedule would be: All the rows except
    >rows where there is another identical PROJ/TASK combination and the EFFDATE
    >is not the highest for that combination.
    >
    >The "select" logic would be something like:
    >
    >SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK combinations
    >where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK

    combination.
    >
    >Could someone show me a valid SELECT statement for this? It seems simple
    >but I've tried many unsucessful satements!
    >
    >Thanks,
    >Roy
    >
    >
    >



  3. #3
    Roy Guest

    Re: Select all rows with highest value of group


    Qbert,

    Thanks.

    I've tried that and several variants of it but it doesn't weed out the unwanted
    rows. I've thought long and hard about why it doesn't and haven't got a
    clue.

    Roy


    "Qbert" <luke_davis_76@hotmail.com> wrote:
    >
    >Roy,
    >You could do
    >
    >SELECT proj, task, Max(effdate), duedate
    >FROM table
    >WHERE
    >GROUP BY Proj, Task, Duedate
    >
    >"Roy" <roy.antetomaso@compaq.com> wrote:
    >>
    >>(Pulling my hair out!!)
    >>I have a table of four columns:
    >>
    >>PROJ - a project name
    >>TASK - a task name
    >>EFFDATE - Effective date of a Due Date
    >>DUEDATE - the acutual due date.
    >>
    >>There can be multiple rows of PROJ/TASK combinations.
    >>
    >>I'd like to select what the current schedule would be: All the rows except
    >>rows where there is another identical PROJ/TASK combination and the EFFDATE
    >>is not the highest for that combination.
    >>
    >>The "select" logic would be something like:
    >>
    >>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK combinations
    >>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK

    >combination.
    >>
    >>Could someone show me a valid SELECT statement for this? It seems simple
    >>but I've tried many unsucessful satements!
    >>
    >>Thanks,
    >>Roy
    >>
    >>
    >>

    >



  4. #4
    Roy Guest

    Re: Select all rows with highest value of group


    What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    error when I try to drop the DUEDATE from the GROUP clause.

    Roy

    "Roy" <roy.antetomaso@compaq.com> wrote:
    >
    >Qbert,
    >
    >Thanks.
    >
    >I've tried that and several variants of it but it doesn't weed out the unwanted
    >rows. I've thought long and hard about why it doesn't and haven't got a
    >clue.
    >
    >Roy
    >
    >
    >"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>
    >>Roy,
    >>You could do
    >>
    >>SELECT proj, task, Max(effdate), duedate
    >>FROM table
    >>WHERE
    >>GROUP BY Proj, Task, Duedate
    >>
    >>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>
    >>>(Pulling my hair out!!)
    >>>I have a table of four columns:
    >>>
    >>>PROJ - a project name
    >>>TASK - a task name
    >>>EFFDATE - Effective date of a Due Date
    >>>DUEDATE - the acutual due date.
    >>>
    >>>There can be multiple rows of PROJ/TASK combinations.
    >>>
    >>>I'd like to select what the current schedule would be: All the rows except
    >>>rows where there is another identical PROJ/TASK combination and the EFFDATE
    >>>is not the highest for that combination.
    >>>
    >>>The "select" logic would be something like:
    >>>
    >>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK combinations
    >>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK

    >>combination.
    >>>
    >>>Could someone show me a valid SELECT statement for this? It seems simple
    >>>but I've tried many unsucessful satements!
    >>>
    >>>Thanks,
    >>>Roy
    >>>
    >>>
    >>>

    >>

    >



  5. #5
    Kevin Verble Guest

    Re: Select all rows with highest value of group


    Maybe something like the following. I don't think sub-selects are considered
    very efficient, but they seem to simplify some queries that are difficult.
    I don't know if you can do a similar thing with a self-join or not.

    SELECT proj, task, effdate, duedate
    FROM table t1
    WHERE effdate in (select max(effdate) from table t2
    where t2.proj = t1.proj and t2.task = t1.task)
    GROUP BY Proj, Task, Duedate

    "Roy" <roy.antetomaso@compaq.com> wrote:
    >
    >What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    >error when I try to drop the DUEDATE from the GROUP clause.
    >
    >Roy
    >
    >"Roy" <roy.antetomaso@compaq.com> wrote:
    >>
    >>Qbert,
    >>
    >>Thanks.
    >>
    >>I've tried that and several variants of it but it doesn't weed out the

    unwanted
    >>rows. I've thought long and hard about why it doesn't and haven't got

    a
    >>clue.
    >>
    >>Roy
    >>
    >>
    >>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>
    >>>Roy,
    >>>You could do
    >>>
    >>>SELECT proj, task, Max(effdate), duedate
    >>>FROM table
    >>>WHERE
    >>>GROUP BY Proj, Task, Duedate
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>(Pulling my hair out!!)
    >>>>I have a table of four columns:
    >>>>
    >>>>PROJ - a project name
    >>>>TASK - a task name
    >>>>EFFDATE - Effective date of a Due Date
    >>>>DUEDATE - the acutual due date.
    >>>>
    >>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>
    >>>>I'd like to select what the current schedule would be: All the rows

    except
    >>>>rows where there is another identical PROJ/TASK combination and the EFFDATE
    >>>>is not the highest for that combination.
    >>>>
    >>>>The "select" logic would be something like:
    >>>>
    >>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK combinations
    >>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>combination.
    >>>>
    >>>>Could someone show me a valid SELECT statement for this? It seems simple
    >>>>but I've tried many unsucessful satements!
    >>>>
    >>>>Thanks,
    >>>>Roy
    >>>>
    >>>>
    >>>>
    >>>

    >>

    >



  6. #6
    Roemisch Guest

    Re: Select all rows with highest value of group


    In Access you can do following
    SELECT proj, task, Max(effdate), first(duedate)
    FROM table
    WHERE
    GROUP BY Proj, Task
    ORDER BY effdate desc

    but SQL-Server didn't understand the first statement

    "Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >
    >Maybe something like the following. I don't think sub-selects are considered
    >very efficient, but they seem to simplify some queries that are difficult.
    >I don't know if you can do a similar thing with a self-join or not.
    >
    >SELECT proj, task, effdate, duedate
    >FROM table t1
    >WHERE effdate in (select max(effdate) from table t2
    > where t2.proj = t1.proj and t2.task = t1.task)
    >GROUP BY Proj, Task, Duedate
    >
    >"Roy" <roy.antetomaso@compaq.com> wrote:
    >>
    >>What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    >>error when I try to drop the DUEDATE from the GROUP clause.
    >>
    >>Roy
    >>
    >>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>
    >>>Qbert,
    >>>
    >>>Thanks.
    >>>
    >>>I've tried that and several variants of it but it doesn't weed out the

    >unwanted
    >>>rows. I've thought long and hard about why it doesn't and haven't got

    >a
    >>>clue.
    >>>
    >>>Roy
    >>>
    >>>
    >>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>
    >>>>Roy,
    >>>>You could do
    >>>>
    >>>>SELECT proj, task, Max(effdate), duedate
    >>>>FROM table
    >>>>WHERE
    >>>>GROUP BY Proj, Task, Duedate
    >>>>
    >>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>
    >>>>>(Pulling my hair out!!)
    >>>>>I have a table of four columns:
    >>>>>
    >>>>>PROJ - a project name
    >>>>>TASK - a task name
    >>>>>EFFDATE - Effective date of a Due Date
    >>>>>DUEDATE - the acutual due date.
    >>>>>
    >>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>
    >>>>>I'd like to select what the current schedule would be: All the rows

    >except
    >>>>>rows where there is another identical PROJ/TASK combination and the

    EFFDATE
    >>>>>is not the highest for that combination.
    >>>>>
    >>>>>The "select" logic would be something like:
    >>>>>
    >>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    combinations
    >>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>combination.
    >>>>>
    >>>>>Could someone show me a valid SELECT statement for this? It seems simple
    >>>>>but I've tried many unsucessful satements!
    >>>>>
    >>>>>Thanks,
    >>>>>Roy
    >>>>>
    >>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  7. #7
    Bob Guest

    Re: Select all rows with highest value of group


    Loose the due date and this works

    SELECT DISTINCT proj, task, MAX(effdate)
    FROM test
    GROUP BY proj, task

    Bob



    "Roemisch" <roemisch@network-design.de> wrote:
    >
    >In Access you can do following
    >SELECT proj, task, Max(effdate), first(duedate)
    >FROM table
    >WHERE
    >GROUP BY Proj, Task
    >ORDER BY effdate desc
    >
    >but SQL-Server didn't understand the first statement
    >
    >"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>
    >>Maybe something like the following. I don't think sub-selects are considered
    >>very efficient, but they seem to simplify some queries that are difficult.
    >>I don't know if you can do a similar thing with a self-join or not.
    >>
    >>SELECT proj, task, effdate, duedate
    >>FROM table t1
    >>WHERE effdate in (select max(effdate) from table t2
    >> where t2.proj = t1.proj and t2.task = t1.task)
    >>GROUP BY Proj, Task, Duedate
    >>
    >>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>
    >>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    >>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>
    >>>Roy
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>Qbert,
    >>>>
    >>>>Thanks.
    >>>>
    >>>>I've tried that and several variants of it but it doesn't weed out the

    >>unwanted
    >>>>rows. I've thought long and hard about why it doesn't and haven't got

    >>a
    >>>>clue.
    >>>>
    >>>>Roy
    >>>>
    >>>>
    >>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>
    >>>>>Roy,
    >>>>>You could do
    >>>>>
    >>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>FROM table
    >>>>>WHERE
    >>>>>GROUP BY Proj, Task, Duedate
    >>>>>
    >>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>
    >>>>>>(Pulling my hair out!!)
    >>>>>>I have a table of four columns:
    >>>>>>
    >>>>>>PROJ - a project name
    >>>>>>TASK - a task name
    >>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>DUEDATE - the acutual due date.
    >>>>>>
    >>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>
    >>>>>>I'd like to select what the current schedule would be: All the rows

    >>except
    >>>>>>rows where there is another identical PROJ/TASK combination and the

    >EFFDATE
    >>>>>>is not the highest for that combination.
    >>>>>>
    >>>>>>The "select" logic would be something like:
    >>>>>>
    >>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    >combinations
    >>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>combination.
    >>>>>>
    >>>>>>Could someone show me a valid SELECT statement for this? It seems

    simple
    >>>>>>but I've tried many unsucessful satements!
    >>>>>>
    >>>>>>Thanks,
    >>>>>>Roy
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  8. #8
    Jason Guest

    Re: Select all rows with highest value of group


    A couple notes about the various approaches presented:

    1.)"first(duedate)": the 'first' aggregate clause is not supported in SQL
    Server.
    2.) "SELECT proj, task, Max(effdate), duedate FROM table WHERE GROUP BY
    Proj, Task, Duedate": This will not necessarily return unique combinations
    of proj,
    consider this record set
    Proj Task EffDate DueDate
    ---------------------------
    A 1 4/11/01 4/12/01
    A 1 4/12/01 4/13/01
    If this data is possible you will get 2 records from the above query because
    of the duedate in the group by clause.
    3.)"SELECT proj, task, effdate, duedate FROM table t1 WHERE effdate in
    (select max(effdate) from table t2 where t2.proj = t1.proj and t2.task =
    t1.task) GROUP BY Proj, Task, Duedate": Kevin has the best approach, in
    my opinion, except that the group by clause on the main query isn't necessary
    (and invalidates the syntax of the query) and removing it could help improve
    the query's performance.

    Alternately, you could write it as a join as follows:
    Select SomeTable.Proj, SomeTable.Task, SomeTable.EffDate, SomeTable.DueDate
    From SomeTable Join
    (Select Proj, Task, Max(EffDate) as MaxEffDate From SomeTable Group By Proj,Task)
    JoinTable
    On (SomeTable.Proj = JoinTable.Proj and SomeTable.Task = JoinTable.Task and
    SomeTable.EffDate = JoinTable.MaxEffDate)

    This might be faster than the subquery because the aggregate query is only
    run once, instead of every row.

    Good luck.


    "Roemisch" <roemisch@network-design.de> wrote:
    >
    >In Access you can do following
    >SELECT proj, task, Max(effdate), first(duedate)
    >FROM table
    >WHERE
    >GROUP BY Proj, Task
    >ORDER BY effdate desc
    >
    >but SQL-Server didn't understand the first statement
    >
    >"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>
    >>Maybe something like the following. I don't think sub-selects are considered
    >>very efficient, but they seem to simplify some queries that are difficult.
    >>I don't know if you can do a similar thing with a self-join or not.
    >>
    >>SELECT proj, task, effdate, duedate
    >>FROM table t1
    >>WHERE effdate in (select max(effdate) from table t2
    >> where t2.proj = t1.proj and t2.task = t1.task)
    >>GROUP BY Proj, Task, Duedate
    >>
    >>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>
    >>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    >>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>
    >>>Roy
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>Qbert,
    >>>>
    >>>>Thanks.
    >>>>
    >>>>I've tried that and several variants of it but it doesn't weed out the

    >>unwanted
    >>>>rows. I've thought long and hard about why it doesn't and haven't got

    >>a
    >>>>clue.
    >>>>
    >>>>Roy
    >>>>
    >>>>
    >>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>
    >>>>>Roy,
    >>>>>You could do
    >>>>>
    >>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>FROM table
    >>>>>WHERE
    >>>>>GROUP BY Proj, Task, Duedate
    >>>>>
    >>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>
    >>>>>>(Pulling my hair out!!)
    >>>>>>I have a table of four columns:
    >>>>>>
    >>>>>>PROJ - a project name
    >>>>>>TASK - a task name
    >>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>DUEDATE - the acutual due date.
    >>>>>>
    >>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>
    >>>>>>I'd like to select what the current schedule would be: All the rows

    >>except
    >>>>>>rows where there is another identical PROJ/TASK combination and the

    >EFFDATE
    >>>>>>is not the highest for that combination.
    >>>>>>
    >>>>>>The "select" logic would be something like:
    >>>>>>
    >>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    >combinations
    >>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>combination.
    >>>>>>
    >>>>>>Could someone show me a valid SELECT statement for this? It seems

    simple
    >>>>>>but I've tried many unsucessful satements!
    >>>>>>
    >>>>>>Thanks,
    >>>>>>Roy
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  9. #9
    Barry Guest

    Re: Select all rows with highest value of group


    TRY THIS...

    SELECT T1.proj, T1.task, T1.effdate, T1.duedate
    FROM TABLE T1
    inner join (select max(effdate)as effdate, PROJ, TASK from TABLE
    group by PROJ, TASK) as T2
    on T1.EffDate = T2.EffDate and T1.PROJ = T2.PROJ AND T1.TASK = T2.TASK


    "Bob" <Bobt@aosus.com> wrote:
    >
    >Loose the due date and this works
    >
    >SELECT DISTINCT proj, task, MAX(effdate)
    >FROM test
    >GROUP BY proj, task
    >
    >Bob
    >
    >
    >
    >"Roemisch" <roemisch@network-design.de> wrote:
    >>
    >>In Access you can do following
    >>SELECT proj, task, Max(effdate), first(duedate)
    >>FROM table
    >>WHERE
    >>GROUP BY Proj, Task
    >>ORDER BY effdate desc
    >>
    >>but SQL-Server didn't understand the first statement
    >>
    >>"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>>
    >>>Maybe something like the following. I don't think sub-selects are considered
    >>>very efficient, but they seem to simplify some queries that are difficult.
    >>>I don't know if you can do a similar thing with a self-join or not.
    >>>
    >>>SELECT proj, task, effdate, duedate
    >>>FROM table t1
    >>>WHERE effdate in (select max(effdate) from table t2
    >>> where t2.proj = t1.proj and t2.task = t1.task)
    >>>GROUP BY Proj, Task, Duedate
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a

    syntax
    >>>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>>
    >>>>Roy
    >>>>
    >>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>
    >>>>>Qbert,
    >>>>>
    >>>>>Thanks.
    >>>>>
    >>>>>I've tried that and several variants of it but it doesn't weed out the
    >>>unwanted
    >>>>>rows. I've thought long and hard about why it doesn't and haven't got
    >>>a
    >>>>>clue.
    >>>>>
    >>>>>Roy
    >>>>>
    >>>>>
    >>>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>>
    >>>>>>Roy,
    >>>>>>You could do
    >>>>>>
    >>>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>>FROM table
    >>>>>>WHERE
    >>>>>>GROUP BY Proj, Task, Duedate
    >>>>>>
    >>>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>>
    >>>>>>>(Pulling my hair out!!)
    >>>>>>>I have a table of four columns:
    >>>>>>>
    >>>>>>>PROJ - a project name
    >>>>>>>TASK - a task name
    >>>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>>DUEDATE - the acutual due date.
    >>>>>>>
    >>>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>>
    >>>>>>>I'd like to select what the current schedule would be: All the rows
    >>>except
    >>>>>>>rows where there is another identical PROJ/TASK combination and the

    >>EFFDATE
    >>>>>>>is not the highest for that combination.
    >>>>>>>
    >>>>>>>The "select" logic would be something like:
    >>>>>>>
    >>>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    >>combinations
    >>>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>>combination.
    >>>>>>>
    >>>>>>>Could someone show me a valid SELECT statement for this? It seems

    >simple
    >>>>>>>but I've tried many unsucessful satements!
    >>>>>>>
    >>>>>>>Thanks,
    >>>>>>>Roy
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  10. #10
    Roy Guest

    Re: Select all rows with highest value of group


    Kevin,

    Many thanks for the response.
    (BTW, I'm really a novice at this so my comments are more suspicion than
    knowledge.)

    However,
    1. The GROUP BY doesn't have context with relation to some aggregate function
    such as the 'max' when it is outside the parens. So I put it inside the
    parens.

    2. Once inside, then the SELECT ... GROUP BY just returned every row. There
    was no mechanism to exclude rows whose 'effdate' was less than the max for
    that proj/task combination.

    Thanks again,
    Roy


    "Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >
    >Maybe something like the following. I don't think sub-selects are considered
    >very efficient, but they seem to simplify some queries that are difficult.
    >I don't know if you can do a similar thing with a self-join or not.
    >
    >SELECT proj, task, effdate, duedate
    >FROM table t1
    >WHERE effdate in (select max(effdate) from table t2
    > where t2.proj = t1.proj and t2.task = t1.task)
    >GROUP BY Proj, Task, Duedate
    >
    >"Roy" <roy.antetomaso@compaq.com> wrote:
    >>
    >>What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    >>error when I try to drop the DUEDATE from the GROUP clause.
    >>
    >>Roy
    >>
    >>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>
    >>>Qbert,
    >>>
    >>>Thanks.
    >>>
    >>>I've tried that and several variants of it but it doesn't weed out the

    >unwanted
    >>>rows. I've thought long and hard about why it doesn't and haven't got

    >a
    >>>clue.
    >>>
    >>>Roy
    >>>
    >>>
    >>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>
    >>>>Roy,
    >>>>You could do
    >>>>
    >>>>SELECT proj, task, Max(effdate), duedate
    >>>>FROM table
    >>>>WHERE
    >>>>GROUP BY Proj, Task, Duedate
    >>>>
    >>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>
    >>>>>(Pulling my hair out!!)
    >>>>>I have a table of four columns:
    >>>>>
    >>>>>PROJ - a project name
    >>>>>TASK - a task name
    >>>>>EFFDATE - Effective date of a Due Date
    >>>>>DUEDATE - the acutual due date.
    >>>>>
    >>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>
    >>>>>I'd like to select what the current schedule would be: All the rows

    >except
    >>>>>rows where there is another identical PROJ/TASK combination and the

    EFFDATE
    >>>>>is not the highest for that combination.
    >>>>>
    >>>>>The "select" logic would be something like:
    >>>>>
    >>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    combinations
    >>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>combination.
    >>>>>
    >>>>>Could someone show me a valid SELECT statement for this? It seems simple
    >>>>>but I've tried many unsucessful satements!
    >>>>>
    >>>>>Thanks,
    >>>>>Roy
    >>>>>
    >>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  11. #11
    Roy Guest

    Re: Select all rows with highest value of group


    I couldn't get the 'empty' WHERE to pass syntax checking in MS Access. It
    kept saying I had an error in the WHERE statement.

    Thanks,
    Roy

    "Roemisch" <roemisch@network-design.de> wrote:
    >
    >In Access you can do following
    >SELECT proj, task, Max(effdate), first(duedate)
    >FROM table
    >WHERE
    >GROUP BY Proj, Task
    >ORDER BY effdate desc
    >
    >but SQL-Server didn't understand the first statement
    >
    >"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>
    >>Maybe something like the following. I don't think sub-selects are considered
    >>very efficient, but they seem to simplify some queries that are difficult.
    >>I don't know if you can do a similar thing with a self-join or not.
    >>
    >>SELECT proj, task, effdate, duedate
    >>FROM table t1
    >>WHERE effdate in (select max(effdate) from table t2
    >> where t2.proj = t1.proj and t2.task = t1.task)
    >>GROUP BY Proj, Task, Duedate
    >>
    >>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>
    >>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    >>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>
    >>>Roy
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>Qbert,
    >>>>
    >>>>Thanks.
    >>>>
    >>>>I've tried that and several variants of it but it doesn't weed out the

    >>unwanted
    >>>>rows. I've thought long and hard about why it doesn't and haven't got

    >>a
    >>>>clue.
    >>>>
    >>>>Roy
    >>>>
    >>>>
    >>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>
    >>>>>Roy,
    >>>>>You could do
    >>>>>
    >>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>FROM table
    >>>>>WHERE
    >>>>>GROUP BY Proj, Task, Duedate
    >>>>>
    >>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>
    >>>>>>(Pulling my hair out!!)
    >>>>>>I have a table of four columns:
    >>>>>>
    >>>>>>PROJ - a project name
    >>>>>>TASK - a task name
    >>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>DUEDATE - the acutual due date.
    >>>>>>
    >>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>
    >>>>>>I'd like to select what the current schedule would be: All the rows

    >>except
    >>>>>>rows where there is another identical PROJ/TASK combination and the

    >EFFDATE
    >>>>>>is not the highest for that combination.
    >>>>>>
    >>>>>>The "select" logic would be something like:
    >>>>>>
    >>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    >combinations
    >>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>combination.
    >>>>>>
    >>>>>>Could someone show me a valid SELECT statement for this? It seems

    simple
    >>>>>>but I've tried many unsucessful satements!
    >>>>>>
    >>>>>>Thanks,
    >>>>>>Roy
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  12. #12
    Roy Guest

    Re: Select all rows with highest value of group


    Bob,

    Thanks - but . . .

    The query doesn't have the duedate in the result, which is important to the
    query. What I want to know is what are the latest (or most current) duedates
    for the projects.

    Thanks
    Roy


    "Bob" <Bobt@aosus.com> wrote:
    >
    >Loose the due date and this works
    >
    >SELECT DISTINCT proj, task, MAX(effdate)
    >FROM test
    >GROUP BY proj, task
    >
    >Bob
    >
    >
    >
    >"Roemisch" <roemisch@network-design.de> wrote:
    >>
    >>In Access you can do following
    >>SELECT proj, task, Max(effdate), first(duedate)
    >>FROM table
    >>WHERE
    >>GROUP BY Proj, Task
    >>ORDER BY effdate desc
    >>
    >>but SQL-Server didn't understand the first statement
    >>
    >>"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>>
    >>>Maybe something like the following. I don't think sub-selects are considered
    >>>very efficient, but they seem to simplify some queries that are difficult.
    >>>I don't know if you can do a similar thing with a self-join or not.
    >>>
    >>>SELECT proj, task, effdate, duedate
    >>>FROM table t1
    >>>WHERE effdate in (select max(effdate) from table t2
    >>> where t2.proj = t1.proj and t2.task = t1.task)
    >>>GROUP BY Proj, Task, Duedate
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a

    syntax
    >>>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>>
    >>>>Roy
    >>>>
    >>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>
    >>>>>Qbert,
    >>>>>
    >>>>>Thanks.
    >>>>>
    >>>>>I've tried that and several variants of it but it doesn't weed out the
    >>>unwanted
    >>>>>rows. I've thought long and hard about why it doesn't and haven't got
    >>>a
    >>>>>clue.
    >>>>>
    >>>>>Roy
    >>>>>
    >>>>>
    >>>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>>
    >>>>>>Roy,
    >>>>>>You could do
    >>>>>>
    >>>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>>FROM table
    >>>>>>WHERE
    >>>>>>GROUP BY Proj, Task, Duedate
    >>>>>>
    >>>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>>
    >>>>>>>(Pulling my hair out!!)
    >>>>>>>I have a table of four columns:
    >>>>>>>
    >>>>>>>PROJ - a project name
    >>>>>>>TASK - a task name
    >>>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>>DUEDATE - the acutual due date.
    >>>>>>>
    >>>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>>
    >>>>>>>I'd like to select what the current schedule would be: All the rows
    >>>except
    >>>>>>>rows where there is another identical PROJ/TASK combination and the

    >>EFFDATE
    >>>>>>>is not the highest for that combination.
    >>>>>>>
    >>>>>>>The "select" logic would be something like:
    >>>>>>>
    >>>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    >>combinations
    >>>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>>combination.
    >>>>>>>
    >>>>>>>Could someone show me a valid SELECT statement for this? It seems

    >simple
    >>>>>>>but I've tried many unsucessful satements!
    >>>>>>>
    >>>>>>>Thanks,
    >>>>>>>Roy
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  13. #13
    Roy Guest

    Re: Select all rows with highest value of group


    Barry,

    Thanks, I got a variant of this to work in MS Access before your response.
    I had to make two queries - one for each of the SELECT statements in your
    example. I kept getting a syntax error in my form. I'll try to get yours
    to work in in a single query.

    But the logic of your approach does produce the results I need.

    Roy





    "Barry" <mailbarry@yahoo.com> wrote:
    >
    >TRY THIS...
    >
    >SELECT T1.proj, T1.task, T1.effdate, T1.duedate
    >FROM TABLE T1
    >inner join (select max(effdate)as effdate, PROJ, TASK from TABLE
    >group by PROJ, TASK) as T2
    >on T1.EffDate = T2.EffDate and T1.PROJ = T2.PROJ AND T1.TASK = T2.TASK
    >
    >
    >"Bob" <Bobt@aosus.com> wrote:
    >>
    >>Loose the due date and this works
    >>
    >>SELECT DISTINCT proj, task, MAX(effdate)
    >>FROM test
    >>GROUP BY proj, task
    >>
    >>Bob
    >>
    >>
    >>
    >>"Roemisch" <roemisch@network-design.de> wrote:
    >>>
    >>>In Access you can do following
    >>>SELECT proj, task, Max(effdate), first(duedate)
    >>>FROM table
    >>>WHERE
    >>>GROUP BY Proj, Task
    >>>ORDER BY effdate desc
    >>>
    >>>but SQL-Server didn't understand the first statement
    >>>
    >>>"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>>>
    >>>>Maybe something like the following. I don't think sub-selects are considered
    >>>>very efficient, but they seem to simplify some queries that are difficult.
    >>>>I don't know if you can do a similar thing with a self-join or not.
    >>>>
    >>>>SELECT proj, task, effdate, duedate
    >>>>FROM table t1
    >>>>WHERE effdate in (select max(effdate) from table t2
    >>>> where t2.proj = t1.proj and t2.task = t1.task)
    >>>>GROUP BY Proj, Task, Duedate
    >>>>
    >>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>
    >>>>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a

    >syntax
    >>>>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>>>
    >>>>>Roy
    >>>>>
    >>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>
    >>>>>>Qbert,
    >>>>>>
    >>>>>>Thanks.
    >>>>>>
    >>>>>>I've tried that and several variants of it but it doesn't weed out

    the
    >>>>unwanted
    >>>>>>rows. I've thought long and hard about why it doesn't and haven't

    got
    >>>>a
    >>>>>>clue.
    >>>>>>
    >>>>>>Roy
    >>>>>>
    >>>>>>
    >>>>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>>>
    >>>>>>>Roy,
    >>>>>>>You could do
    >>>>>>>
    >>>>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>>>FROM table
    >>>>>>>WHERE
    >>>>>>>GROUP BY Proj, Task, Duedate
    >>>>>>>
    >>>>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>>>
    >>>>>>>>(Pulling my hair out!!)
    >>>>>>>>I have a table of four columns:
    >>>>>>>>
    >>>>>>>>PROJ - a project name
    >>>>>>>>TASK - a task name
    >>>>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>>>DUEDATE - the acutual due date.
    >>>>>>>>
    >>>>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>>>
    >>>>>>>>I'd like to select what the current schedule would be: All the rows
    >>>>except
    >>>>>>>>rows where there is another identical PROJ/TASK combination and the
    >>>EFFDATE
    >>>>>>>>is not the highest for that combination.
    >>>>>>>>
    >>>>>>>>The "select" logic would be something like:
    >>>>>>>>
    >>>>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK
    >>>combinations
    >>>>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>>>combination.
    >>>>>>>>
    >>>>>>>>Could someone show me a valid SELECT statement for this? It seems

    >>simple
    >>>>>>>>but I've tried many unsucessful satements!
    >>>>>>>>
    >>>>>>>>Thanks,
    >>>>>>>>Roy
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  14. #14
    Roy Guest

    Re: Select all rows with highest value of group


    Jason,

    Thanks for the input. See my response to Barry's post.

    Your comments are right on target with the frustrations I had and explained
    several of the results I got.

    I got this to work by building 2 queries but would prefer to have a single
    one. I'll be trying your contructs in the next day - they are very close
    to what I tried - but I kept getting syntax errors because I'm not too good
    with SQL. With your example maybe I can get the single query to work.

    Thanks, again.

    Roy



    "Jason" <jkaley@medline.com> wrote:
    >
    >A couple notes about the various approaches presented:
    >
    > 1.)"first(duedate)": the 'first' aggregate clause is not supported in

    SQL
    >Server.
    > 2.) "SELECT proj, task, Max(effdate), duedate FROM table WHERE GROUP BY
    >Proj, Task, Duedate": This will not necessarily return unique combinations
    >of proj,
    > consider this record set
    > Proj Task EffDate DueDate
    > ---------------------------
    > A 1 4/11/01 4/12/01
    > A 1 4/12/01 4/13/01
    >If this data is possible you will get 2 records from the above query because
    >of the duedate in the group by clause.
    > 3.)"SELECT proj, task, effdate, duedate FROM table t1 WHERE effdate in
    >(select max(effdate) from table t2 where t2.proj = t1.proj and t2.task =
    > t1.task) GROUP BY Proj, Task, Duedate": Kevin has the best approach, in
    >my opinion, except that the group by clause on the main query isn't necessary
    >(and invalidates the syntax of the query) and removing it could help improve
    >the query's performance.
    >
    >Alternately, you could write it as a join as follows:
    >Select SomeTable.Proj, SomeTable.Task, SomeTable.EffDate, SomeTable.DueDate
    >From SomeTable Join
    >(Select Proj, Task, Max(EffDate) as MaxEffDate From SomeTable Group By Proj,Task)
    >JoinTable
    >On (SomeTable.Proj = JoinTable.Proj and SomeTable.Task = JoinTable.Task

    and
    >SomeTable.EffDate = JoinTable.MaxEffDate)
    >
    >This might be faster than the subquery because the aggregate query is only
    >run once, instead of every row.
    >
    >Good luck.
    >
    >
    >"Roemisch" <roemisch@network-design.de> wrote:
    >>
    >>In Access you can do following
    >>SELECT proj, task, Max(effdate), first(duedate)
    >>FROM table
    >>WHERE
    >>GROUP BY Proj, Task
    >>ORDER BY effdate desc
    >>
    >>but SQL-Server didn't understand the first statement
    >>
    >>"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>>
    >>>Maybe something like the following. I don't think sub-selects are considered
    >>>very efficient, but they seem to simplify some queries that are difficult.
    >>>I don't know if you can do a similar thing with a self-join or not.
    >>>
    >>>SELECT proj, task, effdate, duedate
    >>>FROM table t1
    >>>WHERE effdate in (select max(effdate) from table t2
    >>> where t2.proj = t1.proj and t2.task = t1.task)
    >>>GROUP BY Proj, Task, Duedate
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a

    syntax
    >>>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>>
    >>>>Roy
    >>>>
    >>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>
    >>>>>Qbert,
    >>>>>
    >>>>>Thanks.
    >>>>>
    >>>>>I've tried that and several variants of it but it doesn't weed out the
    >>>unwanted
    >>>>>rows. I've thought long and hard about why it doesn't and haven't got
    >>>a
    >>>>>clue.
    >>>>>
    >>>>>Roy
    >>>>>
    >>>>>
    >>>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>>
    >>>>>>Roy,
    >>>>>>You could do
    >>>>>>
    >>>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>>FROM table
    >>>>>>WHERE
    >>>>>>GROUP BY Proj, Task, Duedate
    >>>>>>
    >>>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>>
    >>>>>>>(Pulling my hair out!!)
    >>>>>>>I have a table of four columns:
    >>>>>>>
    >>>>>>>PROJ - a project name
    >>>>>>>TASK - a task name
    >>>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>>DUEDATE - the acutual due date.
    >>>>>>>
    >>>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>>
    >>>>>>>I'd like to select what the current schedule would be: All the rows
    >>>except
    >>>>>>>rows where there is another identical PROJ/TASK combination and the

    >>EFFDATE
    >>>>>>>is not the highest for that combination.
    >>>>>>>
    >>>>>>>The "select" logic would be something like:
    >>>>>>>
    >>>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    >>combinations
    >>>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>>combination.
    >>>>>>>
    >>>>>>>Could someone show me a valid SELECT statement for this? It seems

    >simple
    >>>>>>>but I've tried many unsucessful satements!
    >>>>>>>
    >>>>>>>Thanks,
    >>>>>>>Roy
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  15. #15
    Kevin Guest

    Re: Select all rows with highest value of group


    The sub-select takes the place of the aggregate on the main select statement.
    I believe the following two statements would produce the same results other
    than you could get multiple records from the first query if there are duplicate
    effdates for each proj/task combination.

    SELECT proj, task, effdate
    FROM table t1
    WHERE effdate in (select max(effdate) from table t2
    where t2.proj = t1.proj and t2.task = t1.task)

    SELECT proj, task, max(effdate)
    FROM table t1
    GROUP BY Proj, Task

    "Roy" <roy.antetomaso@compaq.com> wrote:
    >
    >Kevin,
    >
    >Many thanks for the response.
    >(BTW, I'm really a novice at this so my comments are more suspicion than
    >knowledge.)
    >
    >However,
    >1. The GROUP BY doesn't have context with relation to some aggregate function
    >such as the 'max' when it is outside the parens. So I put it inside the
    >parens.
    >
    >2. Once inside, then the SELECT ... GROUP BY just returned every row. There
    >was no mechanism to exclude rows whose 'effdate' was less than the max for
    >that proj/task combination.
    >
    >Thanks again,
    >Roy
    >
    >
    >"Kevin Verble" <kevin.verble@greatplains.com> wrote:
    >>
    >>Maybe something like the following. I don't think sub-selects are considered
    >>very efficient, but they seem to simplify some queries that are difficult.
    >>I don't know if you can do a similar thing with a self-join or not.
    >>
    >>SELECT proj, task, effdate, duedate
    >>FROM table t1
    >>WHERE effdate in (select max(effdate) from table t2
    >> where t2.proj = t1.proj and t2.task = t1.task)
    >>GROUP BY Proj, Task, Duedate
    >>
    >>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>
    >>>What I want to do (I think) is to GROUP by PROJ and TASK but I get a syntax
    >>>error when I try to drop the DUEDATE from the GROUP clause.
    >>>
    >>>Roy
    >>>
    >>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>
    >>>>Qbert,
    >>>>
    >>>>Thanks.
    >>>>
    >>>>I've tried that and several variants of it but it doesn't weed out the

    >>unwanted
    >>>>rows. I've thought long and hard about why it doesn't and haven't got

    >>a
    >>>>clue.
    >>>>
    >>>>Roy
    >>>>
    >>>>
    >>>>"Qbert" <luke_davis_76@hotmail.com> wrote:
    >>>>>
    >>>>>Roy,
    >>>>>You could do
    >>>>>
    >>>>>SELECT proj, task, Max(effdate), duedate
    >>>>>FROM table
    >>>>>WHERE
    >>>>>GROUP BY Proj, Task, Duedate
    >>>>>
    >>>>>"Roy" <roy.antetomaso@compaq.com> wrote:
    >>>>>>
    >>>>>>(Pulling my hair out!!)
    >>>>>>I have a table of four columns:
    >>>>>>
    >>>>>>PROJ - a project name
    >>>>>>TASK - a task name
    >>>>>>EFFDATE - Effective date of a Due Date
    >>>>>>DUEDATE - the acutual due date.
    >>>>>>
    >>>>>>There can be multiple rows of PROJ/TASK combinations.
    >>>>>>
    >>>>>>I'd like to select what the current schedule would be: All the rows

    >>except
    >>>>>>rows where there is another identical PROJ/TASK combination and the

    >EFFDATE
    >>>>>>is not the highest for that combination.
    >>>>>>
    >>>>>>The "select" logic would be something like:
    >>>>>>
    >>>>>>SELECT all PROJ/TASK combinations that are unique and all PROJ/TASK

    >combinations
    >>>>>>where the EFFDATE is greater than all other EFFDATEs for that PROJ/TASK
    >>>>>combination.
    >>>>>>
    >>>>>>Could someone show me a valid SELECT statement for this? It seems

    simple
    >>>>>>but I've tried many unsucessful satements!
    >>>>>>
    >>>>>>Thanks,
    >>>>>>Roy
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center