Group by 'title' Order by date error

Group by 'title' Order by date error - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: Group by 'title' Order by date error (/showthread.php?tid=43276)

Group by 'title' Order by date error - El Forum - 07-06-2011

[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..

Group by 'title' Order by date error - El Forum - 07-06-2011

[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/1313120/retrieving-the-last-record-in-each-group

Group by 'title' Order by date error - El Forum - 07-11-2011

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

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

....

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 ..

Group by 'title' Order by date error - El Forum - 07-15-2011

[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

Group by 'title' Order by date error - El Forum - 07-19-2011

[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