Well, where are you and what time of year is it? (We do know what time of year it is, but the answer might be different for someone reading this eight months from now.) The qualities you are looking for in apples for a pie are taste and texture. You want apples that have great flavor and don't turn to mush when you cook them. You don't necessarily just want sweet apples, though. Many people like a mix of sweet and tart apples in their pies.
Ideally, what you want are fresh, locally grown apples (or at least the right kind of apples grown in the right area) that hold their shape when cooked. Golden Delicious apples, which are native to the eastern half of the United States, are indeed delicious if they come from that part of the country. But those grown in the West or in Europe "have all the lure of a Styrofoam Christmas ornament," according to Frank Browning, author of Apple Harvest (Canada, UK). Indeed, (eastern) Golden Delicious apples are the mainstream apples of choice for apple pies. When mixed with the tart Granny Smith apples, they make an excellent pie.
But if you have access to more than just the four or five varieties of apples found in most supermarkets year-round, you can raise your pie to new heights. You may have to be daring and seek out a farmers' market, but if you do, you'll be rewarded with apples that were picked when ripe and have not traveled from the far ends of the Earth.
Among the really good pie apples are Jonathan, Stayman-Winesap, Cox's Orange Pippin, and Jonagold, all of which provide a good mix of sweetness and tartness. Other sweet choices are Braeburn, Fuji, Mutsu, Pink Lady, Suncrisp, Rome Beauty, and Empire. Good tart baking apples include Idared, Macoun, Newton Pippin, and Northern Spy.
What you want to stay away from are the apples that become mushy when cooked. McIntosh and Cortland are the mainstream apples that lead that list.
Now that you've got the apples nailed down, how about a few recipes to consider: