I agree! I've seen Cassandra in production, and learned why it was put there. With it, you still get some of the SQL goodness, but most guarantees are only within one shard.
With that, you let go of the global ordering, because global ordering / single-point serialization just becomes technically infeasible. At that point you usually don't exactly need them, though. You only need local order within a transaction.
With something like Cassandra you get read after write guarantees that I think you don't get with read replicas and SQL databases but I'm not too familiar with the latest there. Also once you start breaking up your SQL databases into shards you lose all SQL goodness across shards (like joins and other properties).
So no free lunches basically. I'd love for there to be something fast, scalable, queryable, transactional etc. etc. Honestly I haven't looked in a while but every open source SQL solution I've seen in the past that claimed to be durable/reliable/scalable doesn't actually do that under failure modes like NoSQL does and pretty much loses either performance or other guarantees or both. Ofcourse NoSQL doesn't solve some of these problems, but at least you know what you're getting into ;)