CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
user969068
user969068

Reputation: 2943

getting last records from mysql

i have a table which store structure like this

id name group_id
1 ABC   1
2 DEF   1
3 GHi   1
4 jkl   2
5 mno   2
6 pqr   2
7 stu   3
8 vwx   3
9 yz0   3

I wants to get records by there last group_id's, in above example as 3 is not always same so first I have to get last group id with another query:

Selct * FROM records ORDER BY group_ID DESC LIMIT 1;
// so here it gets last group-id which is 3

so again I have to perform another query to get those records with last id's 

// $id = 3 // from above records
Select * From records where group_id = $id

is there any better way doing it with single mysql query? i am using codeigniter active records. Thanks

Upvotes: 0

Views: 189

Answers (2)

Strawberry
Strawberry

Reputation: 33935

 SELECT x.*
   FROM my_table x
   JOIN 
      ( SELECT MAX(group_id) max_group_id FROM my_table ) y
     ON y.max_group_id = x.group_id;

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this:

SELECT * 
FROM records 
WHERE group_ID  = (SELECT MAX(group_ID ) 
                   FROM records);

SQL Fiddle Demo

This will give you:

| ID | NAME | GROUP_ID |
------------------------
|  7 |  stu |        3 |
|  8 |  vwx |        3 |
|  9 |  yz0 |        3 |

Upvotes: 4

Related Questions