mysql - Self joining table view -


i have table containing non-unique leads, need group contain unique, recent (column date) leads.

id      lead_id status  date -----   ------  --      ------------------- 26199   666842  ok      2013-06-19 12:00:09  56199   376842  ok      2013-06-19 12:00:09 58322   376842  ok      2013-06-21 12:11:59 60357   376842  ok      2013-06-24 12:22:00 61431   376842  ok      2013-06-25 12:18:02 62365   376842  ok      2013-06-26 12:16:04 63202   376842  ok      2013-06-27 12:14:08 63983   376842  er      2013-06-28 12:12:06

so in example above should have 2 leads result: id 26199 , 63983 both ones max(date) while being group by lead_id.

i tried left joins, max , group aggregation, don't know i'm doing wrong.

select a.lead_id, max(a.created) created rawlead left join rawlead b on b.created = a.created group a.lead_id

unfortunatelly cannot use subqueries, cause need present them in view.

no subqueries :)

select l1.* lead l1 left join lead l2 on l1.date < l2.date , l1.lead_id = l2.lead_id l2.id null

see working live in sqlfiddle.

the left join works on basis when l1.date @ maximum value, there no l2.date greater value , l2 rows values null.


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more