How to show the newest post only

Question

How can i show a list including the field M.hID, but only the newest M.hID depending on M.tDate?

SELECT  T.kId, T.tKode, M.hId, MAX(M.tDate) as newestTDate
FROM _PFaq T
JOIN _mUser M ON T.kId = M.kId AND T.tKode = M.tKode  
group by T.kId, T.tKode, M.hId, M.tDate

See [here](https://stackoverflow.com/questions/tagged/greatest-n-per-group+sql) — , Aug 27 '19 at 09:29

score 0 · Answer 1 · answered Aug 27 '19 at 09:30

0

Remove M.tDate from group by

    SELECT  T.kId, T.tKode, M.hId, MAX(M.tDate) as newestTDate
    FROM _PFaq T
    JOIN _mUser M ON T.kId = M.kId AND T.tKode = M.tKode  
    group by T.kId, T.tKode, M.hId

answered Aug 27 '19 at 09:30

Fahmi

37,315
5
22
31

score 0 · Answer 2 · answered Aug 27 '19 at 09:39

You may try this. By applying row_number() you can assign serial number to all the inserted hID in descending order for particular date. after that select the top record since that is last inserted as you needed.

; with cte as (
SELECT  row_number() over (partition by M.tDate order by M.hID desc) as Slno, T.kId, T.tKode, M.hId, MAX(M.tDate) as newestTDate
FROM _PFaq T
JOIN _mUser M ON T.kId = M.kId AND T.tKode = M.tKode  
group by T.kId, T.tKode, M.hId)
select T.kId, T.tKode, M.hId,  newestTDate from cte where slno = 1

score 0 · Answer 3 · answered Aug 27 '19 at 09:39

another way if support row_number() in your dbms

 select * from ( T.kId, T.tKode, M.hId, M.tDate as newestTDate,row_number()over(partition by M.hID order by M.tDate desc) rn  from
 FROM _PFaq T
    JOIN _mUser M ON T.kId = M.kId AND T.tKode = M.tKode
              ) a where a.rn=1

score 0 · Answer 4 · answered Aug 27 '19 at 09:42

0

if you want M.hId as the field to group some rows with and search maximum value of M.hId between each group, then you should just group by M.hId:

SELECT  T.kId, T.tKode, M.hId, MAX(M.tDate) as newestTDate
FROM _PFaq T
JOIN _mUser M ON T.kId = M.kId AND T.tKode = M.tKode  
group by M.hId

answered Aug 27 '19 at 09:42

mahyard

1,230
1
13
34

and what about: `T.kId, T.tKode`. don't you think that these columns are required in `GROUP BY` clause as they are included in `SELECT` clause in your example – Popeye Aug 27 '19 at 09:47
It's not a rule that all fields in `SELECT` clause have to participate in `GROUP BY` clause. at least in MySQL it isn't. however maybe you are right because the OP have used them in his snippet so they may have an important role there. @Tejash – mahyard Aug 27 '19 at 11:36

score 0 · Accepted Answer · answered Aug 27 '19 at 10:53

This is often approached as a filtering query rather than an aggregation query. You want to join the tables and then filter so only the most recent row is maintained.

There are multiple different ways to do this, but a correlated subquery often has good performance:

select  T.kId, T.tKode, M.hId,M.tDate as newestTDate
from _PFaq T join
     _mUser M 
     on T.kId = M.kId and T.tKode = M.tKode 
where m.tDate = (select max(m2.tDate)
                 from _mUser m2
                 where m2.kId = M.kId and
                       m2.tKode = M.tKode
                );

That said, the two columns you are selecting are in both tables. Unless you are using the T table for filtering, you don't even need the JOIN:

select M.kId, M.tKode, M.hId,M.tDate as newestTDate
from _mUser M 
where m.tDate = (select max(m2.tDate)
                 from _mUser m2
                 where m2.kId = M.kId and
                       m2.tKode = M.tKode
                );

The best index for this query is _mUser(kId, tKode, tDate desc).

How to show the newest post only

5 Answers5