mysql - How can I get the latest two dates from a single column, in a single table, for two separate results of a WHERE clause? -

-

i have query returns 5 columns.

one of columns shows latest date value row 1 column contains either string "seller-contact" or "buyer-contact".

the original code works fine, , looks this.

select   trim(p.title) `property address`,   trim(us.name) `staff`,    date_format(from_unixtime(max( n.datecreated)), '%d-%m-%y') `last contact note`,    datediff(     date_format(from_unixtime(unix_timestamp(now())), '%y-%m-%d'),     date_format(from_unixtime(max(n.datecreated)), '%y-%m-%d')   ) `days since last note`  ias_property `p` left join ias_note_stream `ns` on ns.target_id = p.id left join ias_note `n` on ns.note_id = n.id left join ias_user_staff `us` on p.user_staff_id = us.id p.status = 'sold' , p.isarchived = 0 , us.position = 'sp' , (n.data '%buyer-contact%' or n.data '%seller-contact%') group p.id order max(n.datecreated) asc

i tried achieve above using 2 subqueries each result. not fail or result in error codes. on larger, live, database takes far long (returning 270 rows, i'm assuming sub-query's makes 270^270x time take previously; never finishes hangs).

select trim(p.title) `property address`, trim(us.name) `staff`,  (select date_format(from_unixtime(max(n.datecreated)), '%d-%m-%y')      ias_property `p`     left join ias_note_stream `ns` on ns.target_id = p.id     left join ias_note `n` on ns.note_id = n.id     n.data '%buyer-contact%'     , p.status = 'sold' , p.isarchived = 0     , us.position = 'sp' ) `last buyer contact note`,  (select date_format(from_unixtime(max(n.datecreated)), '%d-%m-%y')      ias_property `p`     left join ias_note_stream `ns` on ns.target_id = p.id     left join ias_note `n` on ns.note_id = n.id     n.data '%seller-contact%'     , p.status = 'sold' , p.isarchived = 0     , us.position = 'sp' ) `last seller contact note`,  datediff(     date_format(from_unixtime(unix_timestamp(now())), '%y-%m-%d'),     date_format(from_unixtime(max(n.datecreated)), '%y-%m-%d') ) `days since last note`  ias_property `p` left join ias_note_stream `ns` on ns.target_id = p.id left join ias_note `n` on ns.note_id = n.id left join ias_user_staff `us` on p.user_staff_id = us.id p.status = 'sold' , p.isarchived = 0 , us.position = 'sp' , (n.data '%buyer-contact%' or n.data '%seller-contact%') group p.id order max(n.datecreated) asc

is there way make simpler can have like

date_format(from_unixtime(max(      n.datecreated n.data '%seller-%' )), '%d-%m-%y') `last buyer contact note;

tldr: how display both results of clause same column 2 separate columns (as a, b) etc?

you can put if inside max function max of values.

date_format(from_unixtime(max(if(n.data '%buyer-contact%',n.datecreated,null))), '%d-%m-%y') `last buyer contact note` date_format(from_unixtime(max(if(n.data '%seller-contact%',n.datecreated,null))), '%d-%m-%y') `last seller contact note`

adding these should not affect performance of original query.


Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more