#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    69
    Rep Power
    4

    A very selective query


    Hey everyone, this is the most complicated i've had to write so i need some help with it.
    i have a db with the following cols: id, link, group(boolean) and time(when the entry was registered in the db)

    What i need to do is to select the last 3 entries (by id is enough) but not including entries where the link is the same, group=1 and the time difference is bigger than 5 seconds.

    Can anyone please help with including explanations?

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    easiest way is to retrieve a number of rows, say 20, sequenced by time descending

    then as you process these in your application language (php or whatever), throw away the ones you don't want

    if you still haven't reached your quota of 3, go get another 20 and lather, rinse, repeat

    trust me, it's a lot easier and more efficient doing it this way than attempting to do that complex filtering with sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    69
    Rep Power
    4
    Hey, this was solved under codeigniter:

    PHP Code:
    $this->db->query
        
    "create temporary table runoff as 
            select * 
            from share_log 
            where to_groupId != 0 
                and from_id = 
    {$this->session->userdata('userid')}
            group by round(UNIX_TIMESTAMP(time)/5), link 
            order by time desc limit 3" 
    ); 

    $this->db->query
        
    "insert into runoff 
        select * 
        from share_log 
        where to_groupId = 0 and from_id = 
    {$this->session->userdata('userid')} 
        order by time desc limit 3" 
    ); 

    $q $this->db 
        
    ->where('from_id'$this->session->userdata('userid')) 
        ->
    where('link !='''
        ->
    order_by('time''desc'
        ->
    limit(3
        ->
    get('runoff'); 

    // kill the temp table 
    $this->db->close(); 

IMN logo majestic logo threadwatch logo seochat tools logo