group and sort

[eluser]mineass[/eluser]
im sure its easy but cant figure it out

table:

post date
173 2011-05-17 22:16:05
173 2011-05-17 00:51:29
156 2011-05-14 21:08:58
156 2011-05-14 20:39:40
156 2011-05-14 20:05:21
156 2011-05-13 02:25:43
167 2011-05-12 21:15:58
156 2011-05-12 16:22:19
170 2011-05-10 23:31:06

i need to get:

post date
173 2011-05-17 22:16:05
156 2011-05-14 21:08:58
167 2011-05-12 21:15:58
170 2011-05-10 23:31:06

only last unigue post, then sort rows i got by date

thank you

[eluser]ηυмвєяσηє[/eluser]
something like this should work:
select post, date from table group by post order by date desc limit 0,4

[eluser]mineass[/eluser]
[quote author="ηυмвєяσηє" date="1306199957"]something like this should work:
select post, date from table group by post order by date desc limit 0,4[/quote]

i tried

$this -> db -> select('post, date') -> group_by('post') -> order_by('date', 'desc')

but it doesnt return last post, seems it sorts by date, then groups by post

[eluser]ηυмвєяσηє[/eluser]
$this->db->select(‘id, post, date’)->group_by(‘post’)->order_by(‘id’, ‘desc’)

try this, last posts would have higher id (auto_inc)

[eluser]mineass[/eluser]
[quote author="ηυмвєяσηє" date="1306200591"]$this->db->select(‘post, date’)->group_by(‘post’)->order_by(‘id’, ‘desc’)

try this, last posts would be higher id (auto_inc)[/quote]

still, it sorts by id (its OK), then groups by post (its OK too) but finally returns first post, not last

feeling myself crazy...

[eluser]mineass[/eluser]
seem i need kind of group + sort

[eluser]ηυмвєяσηє[/eluser]
this should work

Code:

$this->db->query("
      SELECT t1.post, t1.date
      FROM table t1 LEFT JOIN table t2
      ON (t1.post = t2.post AND t1.date < t2.date)
      WHERE t2.date IS NULL; ");

depending on http://stackoverflow.com/questions/13131...each-group

[eluser]mineass[/eluser]
[quote author="ηυмвєяσηє" date="1306201711"]this should work

Code:

$this->db->query("
      SELECT t1.post, t1.date
      FROM table t1 LEFT JOIN table t2
      ON (t1.post = t2.post AND t1.date < t2.date)
      WHERE t2.date IS NULL; ");

depending on http://stackoverflow.com/questions/13131...each-group[/quote]

omg it works, haha lol thank you very much friend!! tell me the address i should send beer ^)