try looking at top end twin lens cameras
They don't make THOSE any more, apart from for some weird phtographers! I've still got one, somewhere.
Larg(ish) sensors will be with us for a long time, it's down to the laws of physics and how greedy people get/remain for image quality.
As I'm sure you know light comes in packets called photons. If you can only grab a few of the ones you actually want on your pixel, the random noise in the system gets in the way. The human eye is , surprisingly, able to detect a single photon of light, which amazed me, I thought there would be huge numbers of them.
So cameras with larger pixels work much better in low light, and phone cameras by comparison are awful. They're getting better, but there is a limit.
That's why Apple don't put 14MP sensors on their iPhone - they know the chip's too small and don't pander to people who don't.
So if ,eg, you want a fast shutter speed, you want a big sensor, and so on. There's lots of "Ah yes but"s, which take quite a bit of learning to understand, you tend to win on one thing as you lose on another. Ultimately you have more flexibility with a bigger sensor.
Look at what the pros are using though, currently in studios up around 50MP on big sensors, and out for sports etc, still mostly on 24 x 36mm.
Top landscapers are still using plate cameras and film, because , yes, it's better for what they need. I used to use those, and it's lurvly. It's true that you can do well with digital though and combine a few, or a few hundred images and get things which would have been impossible a few years ago.
Apart from compacts I have an 18MP on APS-C and a 12 MP on 24 x 36. I used the latter recently in a wedding reception disco, at night, lit only by coloured disco lights, of people dancing. They thought I couldn't be possibly be taking pictures

. It does
weigh and
cost about ten or twenty times as much as my 10:1 Lumix, though.
I expect for normal sensible people, something like micro 4/3rds sensor size will get really good. At the moment though there's a peculiar bunch of cameras which use it. I'm an old groat who still wants to look thgough an actual viewfinder - which counts most of them out!
That camera ericmarc points to is amazing value - about 3 tanks of gas for heavens sake. The next step up would be something like a Lumix DMC-G2, but you're starting to get much bigger lenses, especially if you want a long telephoto.