Hie People I needed some assistance with some SQL logic..is their a way i can filter the last 6 records that are inputed into a database dynamically.i am using the limit command but its static unless i am wrong. Thanks BA
select * from `my_table` order by `id` desc limit 6; assuming that id is incremental, numeric and unique On Fri, Sep 24, 2010 at 12:53 PM, Benjamin <anangwe@gmail.com> wrote:
Hie People
I needed some assistance with some SQL logic..is their a way i can filter the last 6 records that are inputed into a database dynamically.i am using the limit command but its static unless i am wrong.
Thanks BA
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Ben Virus, The script 'Ndwex' sent above is correct. It should sort you out. Kevin On Fri, Sep 24, 2010 at 11:07 AM, Nd'wex Common <flexycat@gmail.com> wrote:
select * from `my_table` order by `id` desc limit 6; assuming that id is incremental, numeric and unique
On Fri, Sep 24, 2010 at 12:53 PM, Benjamin <anangwe@gmail.com> wrote:
Hie People
I needed some assistance with some SQL logic..is their a way i can filter the last 6 records that are inputed into a database dynamically.i am using the limit command but its static unless i am wrong.
Thanks BA
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
If you use a primary key int id that's set to auto_increment you can do a SELECT query that orders by id in reverse, limiting to the last 6 results like so (I admit you'll need something to call this query repeatedly - like a perl/php script called by a cron job - hence the 'static' nature): SELECT * FROM db.table ORDER BY id DESC LIMIT 0,6; Other than that then you can use triggers on row inserts or updates...but I'm also investigating this one. A trigger looks like a better solution :) If you go the cron way you'll need to have a way to track the last row that was worked on - probably store the row id in another field or in a variable in the script - and since you can call scripts in 60sec intervals from cron you can have a loop in the script that runs and sleeps on each iteration for about 1k milliseconds (or less)...hope you get the drift...
You could create a view that selects the last six rows in reverse and then call the view whenever you want to get the data. On Fri, Sep 24, 2010 at 1:27 PM, Haggai Nyang <haggai.nyang@gmail.com>wrote:
If you use a primary key int id that's set to auto_increment you can do a SELECT query that orders by id in reverse, limiting to the last 6 results like so (I admit you'll need something to call this query repeatedly - like a perl/php script called by a cron job - hence the 'static' nature):
SELECT * FROM db.table ORDER BY id DESC LIMIT 0,6;
Other than that then you can use triggers on row inserts or updates...but I'm also investigating this one. A trigger looks like a better solution :)
If you go the cron way you'll need to have a way to track the last row that was worked on - probably store the row id in another field or in a variable in the script - and since you can call scripts in 60sec intervals from cron you can have a loop in the script that runs and sleeps on each iteration for about 1k milliseconds (or less)...hope you get the drift...
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards Brian Ngure
assuming that u want to display the last 6 records, you can *order by id desc limit 6* this is assuming you have an auto incrementing column called id in the table. If you don't just *alter table {tablename} add id serial;* else u can have a timestamp column and sort id desc like above. rgds Muchemi. On Fri, Sep 24, 2010 at 12:53 PM, Benjamin <anangwe@gmail.com> wrote:
Hie People
I needed some assistance with some SQL logic..is their a way i can filter the last 6 records that are inputed into a database dynamically.i am using the limit command but its static unless i am wrong.
Thanks BA
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards http://www.software.co.ke ------------------------------------------------------------------ "My Compiler Is My Life"
participants (6)
-
Benjamin -
Brian Ngure -
Haggai Nyang -
Kevin Oeba -
Nd'wex Common -
Peter Muchemi