Most memorably, I remember waiting over two hours for the Millennium Force pass that gave you a one-hour window to return in order to get in the actual line, which was also several hours with the pass and a breakdown or two. Millennium Force was so busy those first couple of years that it felt like a pop culture event to wait and actually get on it, and it was worth it.
Most pleasantly, the longest I ever waited was my first time on Dragster. It was down so often in 2003 that it was rare to see it testing let alone to ride it. My group decided to camp in front of it on a Sunday after we'd already ridden everything else that weekend, and we eventually got on around 6 p.m. -- seven hours later. Since the line was unofficial and outside the closed Dragster entrance, we'd tag in and out for bathroom breaks and food/drinks (I'd never wait that long otherwise!). And sure enough, it was down again not long after my initial ride.
Most UGH-ly, the original X. I don't hold it against Magic Mountain or the X2 crew today, but the line was "give me my money back" slow. I remember empty trains just sitting for a few minutes before the gates opened, and then loaded trains would also just sit with riders wondering what was going on. I had whatever the premium tier of Flash Pass was at the time and still waited a few hours, but the entire stand-by line was horrifying -- it had to be 5+ hours.
Most understandably UGH-ly, any wild mouse on a Saturday at a packed park. Steer clear!