1

Hi i had a doubt in mysql query

mysql> SELECT * from share5;
+----+-------+--------------+--------+-------+---------------------+
| Id | Price | State        | Symbol | Trade | created_date        |
+----+-------+--------------+--------+-------+---------------------+
| 1  | 120   | maharashtra  | mh     | buy   | 2017-09-12 12:18:11 |
| 1  | 121   | maharashtra  | mh     | buy   | 2017-09-12 12:18:36 |
| 2  | 122   | karnataka    | ka     | sell  | 2017-09-12 12:19:26 |
| 2  | 123   | karnataka    | ka     | sell  | 2017-09-12 12:20:00 |
| 3  | 124   | tamil nadu   | tn     | buy   | 2017-09-12 12:20:30 |
| 3  | 125   | tamil nadu   | tn     | buy   | 2017-09-12 12:20:43 |
| 4  | 127   | andrapradesh | ap     | buy   | 2017-09-12 12:26:26 |
| 4  | 126   | andrapradesh | ap     | buy   | 2017-09-12 12:26:39 |
| 4  | 126   | andrapradesh | ap     | buy   | 2017-09-12 16:57:19 |
+----+-------+--------------+--------+-------+---------------------+

I want to select latest value in index by by comparing 2 id's for eg above i want to filter out latest one by comparing the timestamp. I tried the below one, but its giving only the first added value not latest. Is it possible to compare two ids and filter out the latest one?

mysql> SELECT price,id,state,symbol,trade,created_date from share5 group by id ;
+-------+----+--------------+--------+-------+---------------------+
| price | id | state        | symbol | trade | created_date        |
+-------+----+--------------+--------+-------+---------------------+
| 120   | 1  | maharashtra  | mh     | buy   | 2017-09-12 12:18:11 |
| 122   | 2  | karnataka    | ka     | sell  | 2017-09-12 12:19:26 |
| 124   | 3  | tamil nadu   | tn     | buy   | 2017-09-12 12:20:30 |
| 127   | 4  | andrapradesh | ap     | buy   | 2017-09-12 12:26:26 |
+-------+----+--------------+--------+-------+---------------------+
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
program sample
  • 7
  • 4

3 Answers3

0

To get latest row from each group you can use following query

select t.*
from share5 t
join (
  select Id,max(created_date) created_date
  from share5
  group by Id
) t1 using(Id,created_date)

DEMO

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

It seems like you are missing order_by, you can try 

SELECT price,id,state,symbol,trade,created_date from share5 group by id order by created_date desc;
rypskar
  • 2,012
  • 13
  • 13
0

Try this:

SELECT price,id,state,symbol,trade,created_date from share5 where created_date = (select max(created_date)from Customers) group by id,price,state,symbol,trade,created_date

Fareeda
  • 41
  • 4