Forkert sortering ved FULL OUTER JOIN
Jeg joiner nogle tabeller, heraf en FULL OUTER JOIN - for at finde de records som IKKE er repræsenteret i en given tabel.MEN ak ak - hvis min OUTER JOIN er med, så får jeg ikke den sortering som jeg havde forventet...
Mit query er således :
SELECT TOP 1 a.docid, a.defname, a.status, a.blobdata, c.groupname, c.enterlimit, c.pretext, c.posttext FROM documents AS a INNER JOIN web_defname AS b ON a.defname = b.defname INNER JOIN web_groups AS c ON b.groupid = c.groupid LEFT OUTER JOIN web_timelock AS d ON a.docid = d.docid WHERE a.status = 4 AND (d.id IS NULL OR(d.done = 0 AND DATEDIFF(minute, d.ts, GETDATE()) > 5) ) ORDER BY c.rank, a.createtime;
Konceptet er at jeg opretter en record i tabellen web_timelock, med brugernavn samt docid og et timestamp. Så når brugerne efterspørger den næste fil til behandling, så skal der returneres den næste i rækken - sorteret efter c.rank's værdi - men istedet får jeg en "vilkårlig" i rækkefølgen - jeg sætter vilkårlig i anførselstegn fordi den er ikke vilkårlig, jeg kan bare ikke finde ud af hvilken nøgle den sorteres efter...
Her er et resultat for top 100
2455918 N_SCH_HP 4 <Binary data> Schenker 8 NULL 00017100
2455919 N_SCH_HP 4 <Binary data> Schenker 8 NULL 00017100
2455921 N_SCH_HP 4 <Binary data> Schenker 8 NULL 00017100
2455922 N_SCH_HP 4 <Binary data> Schenker 8 NULL 00017100
2455315 N_LEM 4 <Binary data> Leman 20 NULL
2455567 N_LEM 4 <Binary data> Leman 20 NULL
2454026 N_LMG_11 4 <Binary data> LMG 8 NULL 00070060
2454056 N_LMG_11 4 <Binary data> LMG 8 NULL 00070060
2454194 N_LMG_11 4 <Binary data> LMG 8 NULL 00070060
2456551 N_LMG_11 4 <Binary data> LMG 8 NULL 00070060
2156527 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2156591 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2156672 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2156706 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2156959 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157129 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157180 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157203 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157272 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157367 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157601 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157611 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157663 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157667 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2157782 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2158179 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2158413 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2158530 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2158605 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2158606 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2159028 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2159058 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2159081 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2159354 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2159356 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2159543 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2159813 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160049 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160056 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160066 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160079 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160154 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160478 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160516 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160524 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160533 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160683 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160897 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2160966 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161080 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161186 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161223 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161407 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161587 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161751 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161811 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161939 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161948 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2161969 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2162002 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2162058 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2162460 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2162870 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2162899 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2162909 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2162936 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2163032 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2163656 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2163736 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2163867 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2163870 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2163921 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164134 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164155 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164273 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164737 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164739 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164740 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164746 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164800 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164809 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164826 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164828 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164849 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164856 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164857 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164870 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164896 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164899 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164905 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2164921 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2165313 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2165383 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2165457 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2165652 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2165781 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2165936 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2166145 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2166175 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
2166266 O_TRG_11 4 <Binary data> TRG NULL NULL NULL
