1

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:

  • Message from the original author will have 1 in IS_APPROVAL column and the PARENT_ID is null it means that this is the original message that was sent. IS_CLOSED will contain 1 if the conversation is still open, 2 if the original author can no longer reply to it, null it means the receiver didn't open the message yet.
  • PARENT_ID will contain the ID where the message is replying to.
  • Message reply to the original author will have null in IS_CLOSED column

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
Anthony Horne
  • 2,522
  • 2
  • 29
  • 51
threeFatCat
  • 840
  • 13
  • 30
  • `SELECT *` cannot be combined with `GROUP BY`. It is invalid SQL. MySQL accepts it before version 5.7.5 but [it is documented](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) that the values it picks for columns that do not appear in the `GROUP BY` clause is indeterminate. – axiac May 08 '17 at 05:54
  • 3
    [MySQL](https://dev.mysql.com/doc/refman/5.7/en/) and [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/language-reference) are different, unrelated software packages produced by different companies. Please use the correct tags. – axiac May 08 '17 at 05:54
  • Thanks for the heads up. I was thinking that sql-server means literally the server and it is an sql query. BTW it was added automatically while I was typing the question, so I didn't thought of removing it. – threeFatCat May 08 '17 at 05:58
  • 1
    Click on the [tag:sql-server] tag and read its description. Or just put the mouse cursor over the tag in the list of tags below the question and wait. – axiac May 08 '17 at 05:59
  • In reply to your first comment, I tried selecting "manually" the columns that I needed for query thinking it might affect the query. But it didn't make any changes with the result though. – threeFatCat May 08 '17 at 06:06
  • http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column seems to ask a very similar question – Insomniac May 08 '17 at 06:15
  • `SELECT *` is just a shortcut; it doesn't make any difference if you put the list of fields instead of `*`. In the `SELECT` clause of a `GROUP BY` query you can put only the expressions that appear in the `GROUP BY` clause, [aggregate functions](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html) and columns that are functionally dependent on the columns used in the `GROUP BY` clause. – axiac May 08 '17 at 06:18
  • A `GROUP BY` query **does not** return rows from the table. It **creates** the rows it puts in the result using the data from the table. A `GROUP BY` query alone cannot provide the answer for your question; it can, however, provide some partial results to be used in an outer query (use a correct `GROUP BY` query as a sub-query). This question also belongs to the [tag:greatest-n-per-group] tag. – axiac May 08 '17 at 06:20
  • @TBotV63 the answer looks the same as well. Yes I missed that one. `greatest-n-per-group` tag is new to me. Thank you. – threeFatCat May 08 '17 at 06:33

2 Answers2

2

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.

Insomniac
  • 446
  • 7
  • 15
0

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
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • Yes it is. I didn't create the table structure though. I'm just trying to enhance one part of the whole system. – threeFatCat May 08 '17 at 06:35