mysql - How can I get the id, min and max in the same My SQL query? -

-

i have 2 tables: stops , bus_route_details.

stop_id stop_name       1 ‘c’ cross road junction       2 10th road       3 16th road       4 4th road (golibar)       5 600tenamentgate       6 a.d.modi institute       7 ahansari chowk       8 a.h.school       9 a.p.m.complex      10 a.t.i.      11 aai tuljabhavani chowk/lokhandwala complex      12 aakash ganga society (dharavi)

the table stops stores id , name of each stop. bus_route_details table stores bus_number, stop_id of stop stops table , order in stop appears on route. first stop has order 1 whereas last stop can number 44 if route has 44 stops in total.

bus_number stop_id stop_order          8    2139         30          8     351         31          8    1791         32          8      19         33          8       2         34          8     497         35          8    2024         36          8      20         37          8     404         38          8    1787         39          8     621         40          8    1937         41          8    1941         42          7     509          1          7     788          2          7     996          3          7    1340          4          7    1161          5          7     335          6          7    2296          7          7     891          8

as per above, table shows bus number 7 , 8 start , end. in simple words, distinct list of bus numbers, find stop minimum stop order , maximum stop order each bus.

7, start, end [this how want bus numbers , stop names]

my current query gives me 2 columns: either starting or ending stop_name. how can retrieve 3 columns in same query?

    select bus_number, stop_name bus_route_details, stops `where(bus_number, stop_order) in (select bus_number, max(stop_order)` bus_route_details group bus_number) , stops.stop_id = bus_route_details.stop_id

any suggestions? did union , got 4 results in 2 columns 3 columns this. thank you

one way of doing find min , max in derived table , join stops table , use conditional aggregation flatten result, this:

select      b.bus_number,      max(case when b.stop_order = x.mio b.stop_id end)   min_stop_id,     max(case when b.stop_order = x.mio s.stop_name end) min_stop_name,     max(case when b.stop_order = x.mao b.stop_id end)   max_stop_id,     max(case when b.stop_order = x.mao s.stop_name end) max_stop_name bus_route_details b join (     select bus_number, min(stop_order) mio, max(stop_order) mao     bus_route_details     group bus_number ) x on b.bus_number = x.bus_number , (b.stop_order = x.mio or b.stop_order = x.mao) join stops s on b.stop_id = s.stop_id or b.stop_id = s.stop_id group b.bus_number;

this give result like:

bus_number  min_stop_id min_stop_name        max_stop_id max_stop_name ----------- ----------- -------------------- ----------- -------------------- 7           509         stop 1               891         stop 2 8           351         stop 3               1941        stop 4

Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more