sql - error in mysql query, does not return MIN value -

I am trying to work on this query.

This query is called "OMG Ponze" Was written as an answer by:

The exact difference is calculated in the dates of the questions given below, but then fails to choose ROW (ID1-ID2 joints) with the difference is.

  drop table if existing temp4; Create the table temp4, select x.id1, ​​x.id2, x.YEAR, x.mmdd, x.id3, x.id3_yyar, x.id3_MMDD (SELECT t. *, DATEDIFF (CONCAT (t. Id (4)), '-', LEFT (TID 3_MMDD, 2), '-', right (TID 3_MMDD, 2)), conct (CAST (TIEAR AS Cher (4) ), '-', left (TMMDD, 2), '-', right (TMMDD, 2))) In case of FIF, when @ ID1 = TID1 and @ id2 = TID2 then @ RONNUM: = @ Rownum + 1 ELSE @rownum: RK as = 1 END, @ id1: = t.id1, ​​@ id2: = t.id2 to temp3 t zone (SELECT @rownum: = 0, @ id1 : = 0, @ id2: = 0) by R command TID1, TID2, d Iffere, rand ()) x WHERE x.rk = 1;  

I am using the query to randomize each row within each group defined by the ID1-ID2 pair. I should be YEAR-MMDD (i.e. the full difference between YEAR-MMDD and YEAR_ID3-MMDD_ID3 should be reduced) with the minimum difference in the date of ID3. If the exact is greater than one with the same date, then the query must be randomly selected.

If this table was ...

  ID1 ID2 YEAR MMDD ID3 YEAR_ID3 MMDD_ID3 ------------------- -------------------- 1 2 1991 0821 55 1991 0822 1 2 1991 0821 57 1991 0822 1 2 1991 0821 88 1992 0101 1 3 1990 0131 89 2000 0202 1 3 1990 0131 89 2001 0102  

then the query should be returned

  1,2,1991,0821,55 (or 1,2, 19, 1) , 821,57 - according to random draws) 1,3,199,131,8 9  

I am sticking to a SQL dump of a test table ... < / P>

  if the thumbnail is 'samule'; No 'zero', 'year' year (4) no, zero, `mmdd 'four (4) no id 3` `` id' Four (7) no tap, `id 3_aarar` year (4) no tap,` id 3_MMDD four (4) no tap) engine = mice default charset = latin1; Insert `Temp3` values ​​('1', '2', 1992, '0107', '55', 1991, '0528'); INSERT in `Temp3` values ​​('1', '2', 1992; '0107', '57', 1991; '0701'); Insert `Temp` values ​​('1', '3', 1992, '0107', '88', 2000, '0101'); INSERT in `Temp3` values ​​('1', '3', 1992; '0107', '44', 2000, '0101');  

This is a working solution, thanks to OMG token for your help. SELECT x.id1, ​​x.id2, x.YEAR, x.MMDD, x.id3, x.id3_yEAR, x.id3_MMDD = Case when @ id1 = TID1 and @ id2 = TID2, then @RoNum + 1 BSE1 and RK, AD1: = TID1, AD2: = TID2, FDA Select T *, ABS (DATEDIFF (CONCAT (CAST) (TAD3AEAAR AR Chart (4)), '-', left (TD3mmdd, 2), '-', right (TD3mmdd, 2)), conat (TAIR AR Chair (4)), '-', Left (TMMDD, 2), '-', Right (TMMDD, 2)) .ID1, t.id2, diff, RAND () T, (SELECT @ Rownum: = 0, @ id1: = Null, @ id2: = null) r x x WHERE x.rk = 1; Get link Facebook X Pinterest Email Other Apps Comments Post a Comment Popular Posts IronPython in C#: No module named xmlrpclib - I am trying to create a web application with an interactive console for IronPython. When I try to import xmlrpclib into the General console of IronPython, it works. However, if I use IronPython inside my C # code, then it does not put an exception "a module named xmlrpclib", is it a known issue? Any solution to solve this issue? Here is the code: var testcode = @ "import xmlrpclib; apiarver = xmlrpclib.ServerProxy ('address', allow_none = true); print APIServer.Hello () ; "; Mystream str = newest Mystream (); ScriptEngine Engine = Python. Cretegenine (); Engine. Retyme.io asset output (str, system text encoding. ASCII); Engine. Serial.io.setuperoutput (str, systemtext.edcoding.aspx); ScriptScope scope = engine.CreateScope (); ScriptSource src = engine.CreateScriptSourceFromString (testCode); Src.Execute (scope); Sorry for the silly question, it has come to know that there is a path used by IronPathon inside me. The C # code was not correct. I ... qt creator - QtCreator importing makefile projects - Have you ever tried importing Classic C ++ / project into Qt Creator? Say that I wanted to use some libraries in my GUI project, which must be created. how can I do this? In Qt Creator 2.0.1 you can import projects using only * * Cmake, * .creator, * .pro or * .qmlproject Then switch to cmake and you can import the project into Qt builder (Welcome tab -> Open project ...). Alex php - cURL Cookie and 307 redirect issue - I currently have a script that loads my client using curls on another server Currently, the settings curl_setopt ($ CH, CURLOPT_RETURNTRANSFER, 1); Curl_setopt ($ CH, CURLOPT_FOLLOWLOCATION, TRUE); Curl_setopt ($ ch, CURLOPT_AUTOREFERER, true); Curl_setopt ($ ch, CURLOPT_USERAGENT, $ UserAgent); Curl_setopt ($ CH, CURLOPT_HEADER, 0); $ Usecookie = ROOT_PATH "/public_html/football_parser/cookie.txt"; If ($ usecookie) {if (! Is_writable ($ usecookie)) {return "$ usecookie can not write cookie file, change the file's permission to 777 or read only for windows."; } Curl_setopt ($ ch, CURLOPT_COOKIEJAR, $ usecookie); Curl_setopt ($ ch, CURLOPT_COOKIEFILE, $ usecookie); } $ Output = curl_xac ($ ch); I'm trying to load two example URLs statto.com/football/teams/newcastle-united/2005-2006/results and statto.com/football/teams/newcastle-united/2008-2009/results Without a second load without any problems curl_setopt ($ ch, CURLOPT_FOLLOWLOCATION, TR... Powered by Blogger