The SQL query from hell:

The SQL query from hell:

SELECT count(log_id) as tot, max(log_datetime) as latest,
case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end
FROM  tblLog
WHERE  log_referer LIKE ‘%q=%’

GROUP BY case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end
as
log_referer

ORDER BY tot desc, case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end

Ja meneer, da werkt. Nee meneer, ik ben niet beschaamd. 🙂

Dat geeft bijvoorbeeld voor de referrers sinds vannamiddag ongeveer 15u:





























































































































































































































































































































  query hits last
1. veronique de kock naakt 7 2003-04-08 19:09:27.0
2. veronique de cock 6 2003-04-08 21:52:08.0
3. veronique de cock naakt 4 2003-04-08 18:34:21.0
4. “julie taton nue” 2 2003-04-08 21:14:26.0
5. brahim idool 2003 2 2003-04-08 18:44:04.0
6. brahim van idool 2003 2 2003-04-08 21:13:44.0
7. jolie naaktfotos 2 2003-04-08 19:30:12.0
8. julie taton 2 2003-04-08 15:06:14.0
9. julie taton nue 2 2003-04-08 20:37:51.0
10. monokini 2 2003-04-08 20:09:51.0
11. veronique heeft mooie borsten 2 2003-04-08 17:03:54.0
12. veronique naakt 2 2003-04-08 15:20:48.0
13. “christina aguilera naakt” 1 2003-04-08 14:20:26.0
14. “blowjob contest” 1 2003-04-08 18:01:16.0
15. “for your thighs only” +angel 1 2003-04-08 20:29:23.0
16. “king zog” 1 2003-04-08 15:01:24.0
17. “proxy.telenet.be” 1 2003-04-08 16:19:55.0
18. +sneeuwwitje+bloot 1 2003-04-08 15:43:48.0
19. xp process priority set media player 1 2003-04-08 15:05:44.0
20. 50 jaar smurfen gezien 1 2003-04-08 15:31:31.0
21. amercom weblog 1 2003-04-08 16:04:19.0
22. animal crackers 1 2003-04-08 17:14:31.0
23. arabia dolfijn 1 2003-04-08 19:49:50.0
24. aunt peg 1 2003-04-08 19:06:00.0
25. boygirlbang 1 2003-04-08 17:15:26.0
26. boygirlbang password 1 2003-04-08 18:16:07.0
27. brahim idool 2003 ze is van mij 1 2003-04-08 16:23:45.0
28. de borsten van veronique de kock 1 2003-04-08 16:03:08.0
29. de cock naakt 1 2003-04-08 20:17:50.0
30. de+cock+veronique 1 2003-04-08 19:54:08.0
31. donald knuth 1 2003-04-08 19:08:33.0
32. eerste eredame miss belgie 2003 1 2003-04-08 16:31:42.0
33. happiness bbc2 1 2003-04-08 17:37:48.0
34. hemmerechts+dagboek+sex 1 2003-04-08 16:23:38.0
35. homesite+ serial number 1 2003-04-08 21:35:27.0
36. java + “unknown source” 1 2003-04-08 18:44:56.0
37. jolie van big brother naakt 1 2003-04-08 19:25:06.0
38. julie taton naked 1 2003-04-08 17:19:09.0
39. laserpointer+build 1 2003-04-08 17:11:41.0
40. laserpointer+kopen 1 2003-04-08 17:16:12.0
41. link:www.wired.com/news/conflict/0,2100,58346,00.html 1 2003-04-08 15:50:23.0
42. martine tanghe 1 2003-04-08 20:33:42.0
43. martine tanghe naakt 1 2003-04-08 20:42:06.0
44. midsommer murders 1 2003-04-08 16:03:32.0
45. mike’s apartment +divx 1 2003-04-08 16:20:25.0
46. mikesapartment 1 2003-04-08 22:07:21.0
47. naakte m-kids 1 2003-04-08 15:47:18.0
48. naaktfotos jolie big brother 1 2003-04-08 20:22:11.0
49. olsen twins fucking 1 2003-04-08 17:44:11.0
50. palace of king of ogoni kingdom 1 2003-04-08 15:56:44.0
51. pockey datastor .nl 1 2003-04-08 18:22:06.0
52. romeinse+katapult 1 2003-04-08 15:53:20.0
53. spekpater begraven 1 2003-04-08 18:24:37.0
54. trouw laurent en claire datum 1 2003-04-08 20:29:41.0
55. veronique de cock borsten 1 2003-04-08 17:21:43.0
56. voetreflexologie gent 1 2003-04-08 21:01:21.0
57. what is zog? 1 2003-04-08 19:21:40.0
58. yazidis 1 2003-04-08 18:27:23.0
59. zoe van gastel 1 2003-04-08 20:16:33.0
60. zog 1 2003-04-08 16:56:47.0
61. zog albania 1 2003-04-08 17:38:43.0
62. zwemsters 1 2003-04-08 15:37:38.0

Één reactie op “The SQL query from hell:”

Reacties zijn gesloten.