A very selective query

Dev Shed Forums Sponsor:

September 17th, 2012, 04:31 PM

idofr

Contributing User

Join Date: Jan 2011

Posts: 69

Time spent in forums: 10 h 25 m 38 sec
Reputation Power: 3

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

September 17th, 2012, 09:47 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 19 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

September 18th, 2012, 06:02 AM

idofr

Contributing User

Join Date: Jan 2011

Posts: 69

Time spent in forums: 10 h 25 m 38 sec
Reputation Power: 3

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();

Viewing: Dev Shed Forums > Databases > MySQL Help > A very selective query

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump