0

Please help us we have trouble to get the last recently added distinct row from one of our mysql table and the query includes a cross join too

The scenario is a feedback table and queried every 30 seconds to reply them back. we need to get the latest distinct rows for glancing.

TABLE_1 - PK = id

id | name  
1  | Jhon  
2  | Smith

TABLE_2 - Composite = stamp,id

stamp      | id  | feed                               | feed_for
1467898676 |  1  |  hi                                |  2
1467898678 |  1  |  welcome                           |  2
1467898679 |  2  |  hello                             |  1
1467898680 |  2  |  am intrested in your product      |  1

now we only need the distinct rows from table_2 and the latest one the join we peformed is as shown

select a.id,a.name,b.stamp,b.feed 
from TABLE_1 a, TABLE_2 b 
where b.feed_for=2 and a.id=b.id 
group by a.id 
order by b.stamp desc

It gets unique rows but not the latest one. Please help what would be a efficient and working query

the query i have posted is the fastest one for us(tested on a 2.9 million row table) takes 0.013 secs and its about joining two table while getting unique latest row from one table and joining to get other infos on other table

Rowan San
  • 1,489
  • 1
  • 9
  • 8
  • Please learn to use ANSI JOIN syntax instead of implicit joins. – Barmar Jan 31 '15 at 11:33
  • There isnt any question explains this scenario and the solutions are contrasting, thank you – Rowan San Jan 31 '15 at 11:49
  • I don't see how your question is significantly different from the thousands of other similar questions about getting the latest row for each group. – Barmar Jan 31 '15 at 11:53
  • they are inevitably slow sir – Rowan San Jan 31 '15 at 11:56
  • Add an index on `(id, stamp)` and I think you should be able to speed it up. `(stamp, id)` is not useful for getting the latest timestamp within each group. – Barmar Jan 31 '15 at 11:58
  • Well our team solved it, thank you very much for your time :) – Rowan San Jan 31 '15 at 11:59
  • this is what we came up with and working very well for now `select a.id,a.name,b.stamp,b.feed,max(b.stamp) from TABLE_1 a, TABLE_2 b where b.feed_for=2 and a.id=b.id group by a.id order by b.stamp desc` adding max worked – Rowan San Jan 31 '15 at 12:01
  • That won't always return the `feed` from the same row with `max(stamp)`. – Barmar Jan 31 '15 at 12:03
  • @Barmar I'm having a terribly difficult time finding a solution to this question. The linked duplicate does not involve two tables as this question does. Could you point me to a solution for this question, exactly as asked? The only difference for me is I also need to test that `b.stamp` is less than a specific value. Thanks! – MusikAnimal Dec 30 '15 at 18:09
  • 1
    Here's another question that joins 2 tables and finds the max record per group: http://stackoverflow.com/questions/33299205/mysql-join-select-max-for-all-record/33363569#33363569 – Barmar Dec 30 '15 at 18:12

0 Answers0