Group by 'title' Order by date error

[eluser]crimsun[/eluser]
Hello All,

I have a problem when grouping records - I can't manipulate data inside group. For example,

I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name.

------------------------------
id, name, dtadded
------------------------------
1 name1 2007-10-15 00:00:00
2 name2 2007-10-15 00:00:00
3 name1 2007-10-16 00:00:00
------------------------------

I need to receive 2 results (for each name)
3 - name1 - 2007-10-16 00:00:00
2 - name2 - 2007-10-15 00:00:00

If I use SQL code:

SELECT images.id, images.dtadded, images.name, count(name)
FROM images
GROUP BY `name`
ORDER BY dtadded DESC

I get results
1 - name1 - 2007-10-15 00:00:00
2 - name2 - 2007-10-15 00:00:00

Is anyone suggest me solution for this..Thanks in adavance

Sanju..South D Fire..

[eluser]ηυмвєяσηє[/eluser]

Code:

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

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

[eluser]crimsun[/eluser]
Hello ηυмвєяσηє

Thanks for your reply its work for me i am very very happy ....


But still i have one more problem i am using the full text search in my site you have any idea about this is this work in CI also..

So please do guide me ..

[eluser]crimsun[/eluser]
Hello ηυмвєяσηє

Thanks for your reply for group bu title and order by issues....

But i face some formal problem as i need to display the data in some other tabs also so for all category tab its work fine but in other tab the latest one category is not match so it gives me error and not display the properly...so you have any other solution for this...as i have one which is working only in PHMYADMIN as "group by title DESC" how we can integrate this with CI...please suggest me any solution ...

Thanks in Advance..

Thanks

[eluser]crimsun[/eluser]
Hello All,

Can we able to customize the group by fucntion of the basic CI provided..can we able to add the group by field and also pass the order by desc or asc ...

Please suggest me any solution.

Thanks
Sanju