mysql - SQL Query for ID to filter against date -


basically have query returns data tracking tool. query is:

select `bh`.`bug_id`, `p`.`name` `project`, `p2`.`name` `portfolio`, `u`.`username` `user`,from_unixtime( `bh`.`date_modified` ) `modify_ts`,  case `b`.`category_id` when '0' 'no category' when '1' 'all projects general' when '185' '1. req - requirements' when '186' '2. des - design' when '180' '3. dev - development phase testing' when '181' '4. qat - quality assurance testing' when '182' '5. bat -experian business accecptance testing' when '183' '6. uat - external client user testing' when '184' '7. war - warranty period' when '223' '[mort phase 3] 8. defect density' else `b`.`category_id` end `category`, case `b`.`severity` when '40' 'cosmetic' when '50' 'minor' when '60' 'major' when '70' 'critical' end `severity`, case `b`.`priority`  when '20' 'low' when '30' 'medium' when '40' 'high' when '50' 'very high' end `priority`, case `b`.`resolution` when '10' 'open'  when '20' 'passed testing'  when '60' 'duplicate'  when '70' 'change request'  when '80' 'deferred'  when '90' 'rejected'  end `resolution`, case `b`.`status`  when '10' 'new'  when '20' 'clarification' when '40' 'fixed' when '50' 'open'  when '80' 'ready test'  when '90' 'closed'  end `status` `bugtracker`.`mantis_bug_history_table` `bh` left join `bugtracker`.`mantis_bug_table` `b` on `bh`.`bug_id` = `b`.`id` left join `bugtracker`.`mantis_user_table` `u` on `bh`.`user_id` = `u`.`id`  left join `bugtracker`.`mantis_project_table` `p` on `b`.`project_id` = `p`.`id`  left join `bugtracker`.`mantis_project_hierarchy_table` `ph` on `b`.`project_id` = `ph`.`child_id` left join `bugtracker`.`mantis_project_table` `p2` on `ph`.`parent_id` = `p2`.`id`

and example snippet of output columns removed is:

> 19213 triggers uat    14/08/2014 11:00    major   high   open    new 19213   triggers uat    14/08/2014 11:00    major   high   open    new 19213   triggers uat    14/08/2014 11:00    major   high   open    new 19213   triggers uat    04/08/2014 15:30    major   high   open    new 19213   triggers uat    04/08/2014 15:30    major   high   open    new 19394   triggers uat    15/08/2014 14:24    major   high    open    new 19394   triggers uat    15/08/2014 14:24    major   high    open    new 19394   triggers uat    14/08/2014 10:56    major   high    open    new 19394   triggers uat    14/08/2014 10:56    major   high    open    new 19432   triggers uat    15/08/2014 14:32    major   high    open    new 19432   triggers uat    15/08/2014 14:32    major   high    open    new

what want within query each id (far left column - bh.bug_id), return 1 latest date (3rd column - bh.`date_modified). can throw distinct on top of one's return same if need be.

is there way me filter query essentially?

implemted code added query above : 

from `bugtracker`.`mantis_bug_history_table` `bh` inner join (select `bh`.`bug_id`, max(`bh`.`date_modified`)as `md` `bugtracker`.`mantis_bug_history_table` `bh`  group `bh`.`bug_id`) `max_dates` on `bh`.`bug_id` = `max_dates`.`bug_id` , `bh`.`date_modified` = `max_dates`.`md` left join `bugtracker`.`mantis_bug_table` `b` on `bh`.`bug_id` = `b`.`id`

join max dates this:

select distinct ... `bugtracker`.`mantis_bug_history_table` `bh` inner join (select bug_id, max(date_modified) md mantis_bug_history_table group bug_id) max_dates on bh.bug_id = max_dates.bug_id , bh.date_modified = max_dates.md left join ...

for example , other ways solve this, here's excellent manual entry the rows holding group-wise maximum of column


Comments

Post a Comment

Popular posts from this blog

javascript - Jquery show_hide, what to add in order to make the page scroll to the bottom of the hidden field once button is clicked -

i have button on website when clicked, reveals map. button can seen when page first loads up, when clicked, section holding map slides open , goes out of screen since page loads @ top (normal). want page scroll down bottom of div select when button pushed, if map out of screen. if page scrolled down, , clicks show map button, don't want page jump bottom of map. i tried figure out myself, no luck. seasoned jquery coders able give me guidance? many thanks! map = mapdiv.gmap3("get"); infobox = new infobox({ pixeloffset: new google.maps.size(-220, -310), closeboxurl: '', enableeventpropagation: true }); mapdiv.delegate('.infobox .close','click',function () { infobox.close(); }); jquery(".slidingdiv").hide(); jquery(".show_hide").show(); jquery('.show_hide').click(function(){ lastcenter=map.getcenter(); jquery(".slidingdiv&quo
Read more

javascript - Highcharts multi-color line -

Image
i looking create highchart multi-colored line. if goes down under value, become red, , when comes on value, become green . here picture example: or maybe highcharts have other charts models differentiate high or low level color? you can achieve coloring using lineargradient coloring: color: { lineargradient: { x1: 0, x2: 0, y1: 0, y2: 1 }, stops: [ [0, 'red'], [0.25, 'yellow'], [0.50, 'green'], [0.75, 'yellow'], [1, 'red'] ] } here 0 in stops relates minimum value of series , 1 maximum value, , in between percentages between values. if find percentages according values prior creating chart use correct stops values make relate specific values in data (at least approximately). see this jsfiddle example .
Read more

javascript - Enter key does not work in search box -

tried search guys sorry. input in search box doesnt work (or search) when hit enter when user clicks search button. doing wrong? here code. thanks. <script type="text/javascript"> $(document).ready(function() { $("#submit").click(function() { var url = "http://asla.org/awardssearch.html"; url += "?s=" + $('#googlecse').val(); window.location = url; }); $('#googlecse').keydown(function(event) { if (event.keycode == '13') { $("#submit").click(); } else { //do nothing :) } }); }); </script> <form class="navbar-form navbar-right" role="search"> <div class="search"> <input id="googlecse" type="text" onblur="if(this.value=='')this.value=this.defaultvalue;" onfocus="if(this.value==this.defaultvalue)t
Read more