php - Selecting one distinct column, I.E Finetuning select query -


i use bellow query merge 1 (or possibly sevral tables , generated php script have written) on id's not same parts of id in first table part of second id thats why substring on it. in perfect world left join on id in both tables if there same they're not.

select t0.id,t0.customername,t0.region,t0.country,t0.stoptime,t0.customerid,t1.id, t1.time (select distinct id,customername,region,country, stoptime,customerid [dbcust].[dbo].[_content]) t0  inner join  (select distinct id, time [dbcust].[dbo].[_cpu]) t1          on substring(t1.id,charindex('_',t1.id,10)+1,(charindex('_',t1.id,15) - charindex('_',t1.id,10)-1))=substring(t0.id,charindex('_',t0.id,10)+1,(charindex('_',t0.id,15) - charindex('_',t0.id,10)-1)) order t1.time desc

here alot of fields same except stoptime, see example bellow: 

       stoptime                   time 2015-04-01 23:59:00.000    2015-04-18 23:00:01 2015-04-02 23:59:00.000    2015-04-18 23:00:01 2015-04-03 23:59:00.000    2015-04-18 23:00:01 2015-04-04 23:59:00.000    2015-04-18 23:00:01 2015-04-05 23:59:00.000    2015-04-18 23:00:01 2015-04-06 23:59:00.000    2015-04-18 23:00:01 2015-04-07 23:59:00.000    2015-04-18 23:00:01 2015-04-08 23:59:00.000    2015-04-18 23:00:01 2015-04-09 23:59:00.000    2015-04-18 23:00:01 2015-04-10 23:59:00.000    2015-04-18 23:00:01 2015-04-11 23:59:00.000    2015-04-18 23:00:01 2015-04-12 23:59:00.000    2015-04-18 23:00:01 2015-04-13 23:59:00.000    2015-04-18 23:00:01 2015-04-14 23:59:00.000    2015-04-18 23:00:01 2015-04-15 23:59:00.000    2015-04-18 23:00:01 2015-04-16 23:59:00.000    2015-04-18 23:00:01 2015-04-17 23:59:00.000    2015-04-18 23:00:01 2015-04-18 23:59:00.000    2015-04-18 23:00:01

but here want unique time, possible row unique time , latest stoptime?

like bellow?

        stoptime                   time 2015-04-01 23:59:00.000    2015-04-18 23:00:01

i tried group by statement inside second select statement like:

(select distinct id,customername,region,country, stoptime,customerid [dbcust].[dbo].[_content] group stoptime)

but syntax error

column 'dbcust.dbo._content.id' invalid in select list because not contained in either aggregate function or group clause.

perhaps guys can me finetuning select query in order speed data gathering? =)

thanks in advance.

you can this:

with yourquery (     select t0.id,t0.customername,t0.region,t0.country,t0.stoptime,t0.customerid,t1.id, t1.time     (select distinct id,customername,region,country, stoptime,customerid [dbcust].[dbo].[_content]) t0      inner join      (select distinct id, time [dbcust].[dbo].[_cpu]) t1              on substring(t1.id,charindex('_',t1.id,10)+1,(charindex('_',t1.id,15) - charindex('_',t1.id,10)-1))=substring(t0.id,charindex('_',t0.id,10)+1,(charindex('_',t0.id,15) - charindex('_',t0.id,10)-1))  ), ranked (    select id,      customername,      region,      country,      stoptime,      customerid,      id,       time,      row_number() over(partition time order stoptime desc) r1      yourquery ) select id,      customername,      region,      country,      stoptime,      customerid,      id,       time ranked rn = 1;

the row_number function give ranking number each time, selecting where rn = 1 give latest stoptime.

for query tried:

select distinct id,customername,region,country,     stoptime,customerid  [dbcust].[dbo].[_content]  group stoptime

in sql server, when group by column, can't select column unless in group clause or in aggregate function, in order write correctly should this:

select stoptime, min(customerid) -- example [dbcust].[dbo].[_content]  group stoptime

or using full query can this:

select stoptime, max(time) latesttime (     select t0.id,t0.customername,t0.region,t0.country,t0.stoptime,t0.customerid,t1.id, t1.time             (select distinct id,customername,region,country, stoptime,customerid [dbcust].[dbo].[_content]) t0              inner join              (select distinct id, time [dbcust].[dbo].[_cpu]) t1                      on substring(t1.id,charindex('_',t1.id,10)+1,(charindex('_',t1.id,15) - charindex('_',t1.id,10)-1))=substring(t0.id,charindex('_',t0.id,10)+1,(charindex('_',t0.id,15) - charindex('_',t0.id,10)-1))  ) t group stoptime

this give exact result looking for, stoptime , latest time.

so select other columns as 2 columns used ranking function.


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more