CODEWITHSUNDEEP
mysql
user2046410
user2046410

Reputation: 117

Getting the last record with Where clause in Codeigniter

I have a table like this

id    username        sb
1     user1_0001      1
2     user1_0002      1
3     user1_0003      1
4     user2_0001      2 
5     user2_0002      2
6     user2_0003      2
7     user3_0001      3
8     user3_0002      3
9     user3_0003      3
10    user3_0004      3

how can i query for example i want to get the last record of sb = 1 the output should be user1_0003?

Upvotes: 0

Views: 3881

Answers (2)

user2046410
user2046410

Reputation: 117

Since I am working in Codeigniter.

This is my solution thanks to chandimark

$query = $this->db->query("SELECT * FROM user WHERE id = (SELECT MAX(id) FROM user WHERE sb = '".$sb_id."')");
if ($query->num_rows()== 1)
{
    $row = $query->row();
    echo $row->username;
}

Upvotes: 0

chandimak
chandimak

Reputation: 201

Assuming that your table name is 'table1'. In the inner query it returns the max value of id for sb = 1 and then in outer query we select the record matching returned value.

SELECT * FROM table1 WHERE id = (SELECT MAX(id) FROM table1 WHERE sb = 1);

Upvotes: 2

Related Questions