It really depends on what you're looking for. If you're looking for the best sound, then more towards the middle is the best place to be.
If you want the most excitement, then being in the front row is your best bet. But don't bank on the sound being great in the front row. Somehow I got front row seats to see DT at the Pantages (in Los Angeles) in 2006, and while it was awesome making personal eye contact with the guys in the band, the sound sucked - I was on JP's side, so all I heard was guitars and some of MP's drums acoustically. That said, there are some (but not all) venues that have small speakers along the edge of the stage so that those in the front rows still get good sound. I know the Oakdale Theatre in Wallingford, CT has them, so I never had a problem being near the stage for shows at that venue.