views:

140

answers:

4

Suppose we have the following table data:

ID   parent    stage     submitted
1      1         1           1
2      1         2           1
3      1         3           0
4      1         4           0
5      5         1           1
6      5         2           1
7      5         3           1
8      5         4           1

As you can see we have 2 groups (that have the same parent). I want to select the latter stage that is submitted. In the above example i want to select the ID`s 2 and 8. I am completely lost so if anyone can help it will be appreciated a lot. :)

A: 
SELECT * FROM Table WHERE ID = 2 OR ID = 8

Is this what you want?

Daniel A. White
No i am sorry. see my comment i am trying to explain a bit better.
Constandinos
+8  A: 
SELECT T.ID, T.PARENT, T.STAGE 
from
   T, 
   (
      select PARENT, MAX( STAGE) MAX_STAGE 
      from T
      where SUBMITTED = 1
      GROUP BY PARENT 
   ) M
where 
   T.STAGE = M.MAX_STAGE 
   AND T.PARENT = M.PARENT

Explanation: First, isolate the max stage for each group with submitted = 1 (the inner select). Then, join the result with the real table, to filter out the records with no max stage.

Cătălin Pitiș
Excellent Catalin, fast response and with very good explanation.
Constandinos
+1  A: 
Select Parent, max(Id)
From tbl t
Inner Join
(
    Select Parent, max(Stage) as Stage
    from tbl t
    Where Submitted = 1
    Group by Parent
) submitted
on t.Parent = submitted.parent and
    t.stage = submitted.stage
Group by Parent
Don't you need to specify "t.parent" rather than just "parent" in the outer group by/select parts?
araqnid
+1  A: 

This should do it:

SELECT
     T1.id,
     T1.parent,
     T1.stage,
     T1.submitted
FROM
     Some_Table T1
LEFT OUTER JOIN Some_Table T2 ON
     T2.parent = T1.parent AND
     T2.submitted = 1 AND
     T2.stage > T1.stage
WHERE
     T1.submitted = 1 AND
     T2.id IS NULL
Tom H.
+1 just for using a join and no aggregation, although I prefer to make the anti-join explicit: select id, parent, stage from t where submitted = 1 and not exists (select 1 from t t2 where t2.parent = t.parent and t2.submitted = 1 and t2.stage > t.stage);
araqnid
Do the query plans tend to be the same with the NOT EXISTS? Since it's a correlated subquery it seems like it would take a pretty big performance hit for large sets of data.
Tom H.
Oracle, Postgres and SQL Server each produce a very similar join plan for both queries, but qualify the "not exists" version as being an anti-join, whereas the former version has a filter outside a left join. So I'm inclined to say that "not exists" is communicating the query intent better--- but haven't tried any large data sets.
araqnid