sql - PostgreSQL: How to return rows with respect to a found row (relative results)? -


forgive example if not make sense. i'm going try simplified 1 encourage more participation.

consider table following:

  •        dt     |    mnth    |  foo --------------+------------+--------   2012-12-01  |  december  |     ...   2012-08-01  |  august    |   2012-07-01  |  july      |   2012-06-01  |  june      |   2012-05-01  |  may       |   2012-04-01  |  april     |   2012-03-01  |  march     |     ...   1997-01-01  |  january   |

if record dt closest today w/o going over, best way return 3 records beforehand , 7 records after?

i decided try windowing functions:

  • with dates (    select  row_number() on (order dt desc)          , dt          , dt - now()::date dt_diff       foo ) , closest_date (    select * dates    dt_diff = ( select max(dt_diff) dates dt_diff <= 0 ) )  select *    dates  row_number - (select row_number closest_date) >= -3    , row_number - (select row_number closest_date) <=  7 ;

i feel there must better way return relative records window function, it's been time since i've looked @ them.

create table foo (dt date); insert foo values ('2012-12-01'), ('2012-08-01'), ('2012-07-01'), ('2012-06-01'), ('2012-05-01'), ('2012-04-01'), ('2012-03-01'), ('2012-02-01'), ('2012-01-01'), ('1997-01-01'), ('2012-09-01'), ('2012-10-01'), ('2012-11-01'), ('2013-01-01') ;  select dt ( (     select dt     foo     dt <= current_date     order dt desc     limit 4 ) union (     select dt     foo     dt > current_date     order dt     limit 7 )) s order dt ;      dt      ------------  2012-03-01  2012-04-01  2012-05-01  2012-06-01  2012-07-01  2012-08-01  2012-09-01  2012-10-01  2012-11-01  2012-12-01  2013-01-01 (11 rows)

Comments

Post a Comment

Popular posts from this blog

jquery - Invalid Assignment Left-Hand Side -

i trying assign class tag via jquery, resulting in error through firebug says "invalid assignment left-hand side" on fourth line below. <cfoutput> $('.pngfix a').each(function(index) { var hreflink = $(this).attr("href"); if (hreflink.tolowercase() = '../audio.cfm') { $(this).addclass('audioimg'); } }); </cfoutput> looking around online, looks syntax problem, don't seem see it... any tips? thanks if (hreflink.tolowercase() = '../audio.cfm') that attempts assign value, rather check equality. change = operator == .
Read more

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more