This is a good question. If you think about it, patrol can be boring. They are going around to make sure that all is in order. We can say that patrol is preventative. Moreover, nothing may happen. So, a lot of it is waiting and observing. This can be hard on a person day in and day out. Some may even feel that they are not accomplishing great. This is far from the truth, as police enforcement is essential for society. However, it can feel that way.
Investigation is much more active. A crime has taking place and now what investigators do is to see how they can solve the crime. The solving element requires work and thought. There is an immediate feeling of significance and meaning. I think it is the last element that makes it more prestigious.
Apart from this, from the point of view of supply and demand, more people want to be investigators than patrol officers. This alone make investigation more prestigious.