CODEWITHSUNDEEP
mysqlperformanceselect
Ruel
Ruel

Reputation: 15780

Getting latest rows in MySQL based on date (grouped by another column)

This type of question is asked every now and then. The queries provided works, but it affects performance.

I have tried the JOIN method:

SELECT * 
FROM nbk_tabl
INNER JOIN (
        SELECT ITEM_NO, MAX(REF_DATE) as LDATE
        FROM nbk_tabl 
        GROUP BY ITEM_NO) nbk2 
ON nbk_tabl.REF_DATE = nbk2.LDATE 
AND nbk_tabl.ITEM_NO = nbk2.ITEM_NO

And the tuple one (way slower):

SELECT * 
FROM nbk_tabl
WHERE REF_DATE IN (
                    SELECT MAX(REF_DATE) 
                    FROM nbk_tabl 
                    GROUP BY ITEM_NO
                  )

Is there any other performance friendly way of doing this?

EDIT: To be clear, I'm applying this to a table with thousands of rows.

Upvotes: 1

Views: 98

Answers (2)

valex
valex

Reputation: 24144

Also in MySQL you can use user variables (Suppose nbk_tabl.Item_no<>0):

select *
from (
   select nbk_tabl.*,
      @i := if(@ITEM_NO = ITEM_NO, @i + 1, 1) as row_num,
      @ITEM_NO := ITEM_NO as t_itemNo
  from nbk_tabl,(select @i := 0, @ITEM_NO := 0) t
  order by Item_no, REF_DATE DESC
) as x where x.row_num = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Yes, there is a faster way.

select *
from nbk_table
order by ref_date desc
limit <n>

Where is the number of rows that you want to return.

Hold on. I see you are trying to do this for a particular item. You might try this:

select *
from nbk_table n
where ref_date = (select max(ref_date) from nbk_table n2 where n.item_no = n2.item_no)

It might optimize better than the "in" version.

Upvotes: 2

Related Questions