sql - Select top N from group records -


i need list frist record when price changed. using below example.

invoice table records (group customer price , sorted date)

index  customer date         price 01            may-01-2016  $12.00 02            may-11-2016  $12.00  03            may-21-2016  $13.00 04            may-22-2016  $13.00 05            may-23-2016  $13.00 06            may-24-2016  $13.00  07            jun-01-2016  $14.00 08            jun-11-2016  $14.00 09            jun-21-2016  $14.00 10            jun-25-2016  $14.00  11     b        may-02-2016  $12.50 12     b        may-12-2016  $12.50  13     b        may-22-2016  $13.50  14     b        may-24-2016  $13.80 15     b        may-26-2016  $13.80 16     b        may-28-2016  $13.80  17     b        jun-02-2016  $14.60 18     b        jun-12-2016  $14.60 19     b        jun-22-2016  $14.60 20     b        jun-26-2016  $14.60

i need first record when price changed. result be:

01            may-01-2016  $12.00  03            may-21-2016  $13.00  07            jun-01-2016  $14.00  11     b        may-02-2016  $12.50  13     b        may-22-2016  $13.50  14     b        may-24-2016  $13.80  17     b        jun-02-2016  $14.60

further query, can limited result latest 2 records each customer? result be:

03            may-21-2016  $13.00  07            jun-01-2016  $14.00  14     b        may-24-2016  $13.80  17     b        jun-02-2016  $14.60

thank you.

;with cte ( select * ,    cast(substring([date], 5,2) + '-'                  + left([date] , 3)                  + '-' + right([date] ,4) date) datedt invoice) ,cte2  ( select *      ,row_number() on (partition price , customer order datedt asc) rn cte ) select [index] ,customer, [date] ,price cte2 rn = 1 order customer , datedt

result set

╔═══════╦══════════╦═════════════╦═══════╗ ║ index ║ customer ║    date     ║ price ║ ╠═══════╬══════════╬═════════════╬═══════╣ ║    01 ║        ║ may-01-2016 ║ 12.00 ║ ║    03 ║        ║ may-21-2016 ║ 13.00 ║ ║    07 ║        ║ jun-01-2016 ║ 14.00 ║ ║    11 ║ b        ║ may-02-2016 ║ 12.50 ║ ║    13 ║ b        ║ may-22-2016 ║ 13.50 ║ ║    14 ║ b        ║ may-24-2016 ║ 13.80 ║ ║    17 ║ b        ║ jun-02-2016 ║ 14.60 ║ ╚═══════╩══════════╩═════════════╩═══════╝

-

Comments

Post a Comment

Popular posts from this blog

OpenCV OpenCL: Convert Mat to Bitmap in JNI Layer for Android -

there several posts converting mat bitmap using utils.mattobitmap() function. i'm assuming function can called in java layer after importing utils class. i want transfer data memory address pointed uint32_t* bmpcontent; in code below. jniexport void jnicall java_com_nod_nodcv_nodcvactivity_runfilter( jnienv *env, jclass clazz, jobject outbmp, jbytearray indata, jint width, jint height, jint choice, jint filter) { int outsz = width*height; int insz = outsz + outsz/2; androidbitmapinfo bmpinfo; if (androidbitmap_getinfo(env, outbmp, &bmpinfo) < 0) { throwjavaexception(env,"gaussianblur","error retrieving bitmap meta data"); return; } if (bmpinfo.format != android_bitmap_format_rgba_8888) { throwjavaexception(env,"gaussianblur","expecting rgba_8888 format"); return; } uint32_t* bmpcontent; if (androidbitmap_lockpixels(env, outbmp,(void**)&bmpcontent)...
Read more

android - org.xmlpull.v1.XmlPullParserException: expected: START_TAG {http://schemas.xmlsoap.org/soap/envelope/}Envelope -

i trying hit soap api in application login purpose , getting above mentioned error , can 1 tell me going wrong , first time soap apis. here code hit soap :- vector<object> args = new vector<object>(); hashtable<string, object> hashtable = new hashtable<string, object>(); hashtable.put("email", "mss.siddhart28@gmail.com"); hashtable.put("password", "siddharth"); args.add(hashtable); soapserializationenvelope envelope = new soapserializationenvelope( soapenvelope.ver11); envelope.dotnet = true; envelope.xsd = soapserializationenvelope.xsd; envelope.enc = soapserializationenvelope.enc; (new marshalhashtable()).register(envelope); httptransportse androidhttptransport = new httptransportse( "http://tempuri.org/", 15000); androidhttptransport.debug = true; string result = ""; try { soapobject request = new soapobject("http:...
Read more

python - How to remove the Xframe Options header in django? -

i have made page has iframe . inside iframe want show multiple different links article facebook, or news, or youtube video or other possible url. but, due xframe header, unable so. referred following link: https://docs.djangoproject.com/en/1.8/ref/clickjacking/ , django xframeoptionsmiddleware (x-frame-options) - allow iframe client ip but didn't help. my settings.py file's middleware_classes is: middleware_classes = ( 'django.contrib.sessions.middleware.sessionmiddleware', 'django.middleware.common.commonmiddleware', 'django.middleware.csrf.csrfviewmiddleware', 'django.contrib.auth.middleware.authenticationmiddleware', 'django.contrib.auth.middleware.sessionauthenticationmiddleware', 'django.contrib.messages.middleware.messagemiddleware', 'django.middleware.clickjacking.xframeoptionsmiddleware', ) from http://django-secure.readthedocs.org/en/latest/middleware.html , found using ...
Read more