CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
threeFatCat
threeFatCat

Reputation: 819

Select the unique/distinct row of the table with specific condition but using GROUP BY

Actually I really don't know the appropriate title that will makes it unique as a question. Believe me, I tried my best to search about inner join, union, distinct just to make my query done.

I only have one table and it looks like this: 

ID | ITEM | MESSAGE_INFO | PARENT_ID | IS_CLOSED | IS_APPROVAL
1  | A123 |   test 1     |   null    |   1       |     1
2  | A123 | reply to..   |    1      |   null    |    null
3  | A123 |another reply.|    1      |   null    |    null
4  | B456 |   test 2     |    null   |   null    |     1
5  | A123 | new test 1   |    1      |   null    |     1
6  | C789 |  test 3      |   null    |    2      |     1
7  | C789 | reply to 3   |    6      |   null    |    null

Note:

Now what I want to do is I want to get the most recent message from the original author for each item. So the expected result is like this:

ID | ITEM | MESSAGE_INFO | PARENT_ID | IS_CLOSED | IS_APPROVAL
5  | A123 | new test 1   |    1      |   null    |     1
4  | B456 |   test 2     |    null   |   null    |     1

I tried this query:

SELECT *
FROM TABLE
WHERE IS_APPROVAL = 1
    AND (
        IS_CLOSED IS NULL
        OR IS_CLOSED < 2
        )
GROUP BY ITEM
ORDER BY ID DESC;

But the result I'm getting is this: 

ID | ITEM | MESSAGE_INFO | PARENT_ID | IS_CLOSED | IS_APPROVAL
1  | A123 |   test 1     |    null   |   1       |     1
4  | B456 |   test 2     |    null   |   null    |     1

Upvotes: 1

Views: 194

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

Is this real table structure ?

declare @t table(ID int,ITEM varchar(50),MESSAGE_INFO varchar(50)
, PARENT_ID int, IS_CLOSED int,IS_APPROVAL int)
insert into @t VALUES
(1  ,'A123','   test 1     ',null  ,1     , 1   )
,(2  ,'A123',' reply to..   ', 1    ,null  ,null )
,(3  ,'A123','another reply.', 1    ,null  ,null )
,(4  ,'B456','   test 2     ', null ,null  , 1   )
,(5  ,'A123',' new test 1   ', 1    ,null  , 1   )
,(6  ,'C789','  test 3      ',null  , 2    , 1   )
,(7  ,'C789',' reply to 3   ', 6    ,null  ,null )

;With CTE as
(
select *,ROW_NUMBER()over(partition by item order by id desc)rn 
from @t
where IS_APPROVAL = 1 AND (IS_CLOSED IS NULL OR IS_CLOSED<2) 
)
select * from cte where rn=1

Upvotes: 0

Insomniac
Insomniac

Reputation: 446

This should do the trick:

SELECT tab.* FROM tab
INNER JOIN (SELECT MAX(ID) as ID FROM tab WHERE IS_APPROVAL = 1 AND (IS_CLOSED IS NULL OR IS_CLOSED<2) GROUP BY ITEM) ids
ON tab.ID = ids.ID;

It will first determine the highest ID for each item group (as a measure of recency) and then perform a join on itsself.

Upvotes: 2

Related Questions