You say you tried the things I posted, but you didn't at all
I do try things like this. At first I said ‘please get up off the floor, it’s dirty’ and please get up, people can’t get past you’. Then I said, please wait for your sister (who was in the toilet), to come back to the table.
This is far too many pleases. I don't say "please" for this sort of thing. It's not a request, it's an instruction. Please is for "please can you pass me the salt" not "get off the floor, someone will trip over you."
You're also asking him to do something far too many times. It's "up off the floor now" and if he doesn't, lift him and sit him at the table and immediately move on to something else. You don't need to be combative with him, keep it light and breezy: "we sit on our bottoms at the table. Look at these lovely flowers, shall we smell them? I like this one the best."
He didn’t get up. That’s when I picked him up and carried him to the table, then the tantrum started.
The tantrum happened because you pleaded with him over and over and let everything build up. Stop asking him nicely. Tell him and then lift him when he doesn't. He knows it's worth ignoring you because he's sussed you don't immediately follow through and you don't handle tantrums well.
The outside wasn’t just to hand, it was a cafe in an upstairs part of Tesco.
Outside doesn't mean literally outside. Just remove him from the general dining area. If my son acts up in any kind of cafe or restaurant he's removed to the nearest spot away from other diners and I get down to his level and have a very firm word with him. No shouting, but he knows I mean business. I will say to him "we do not sit on the floor of the cafe. It's dangerous, people might trip over you. where are we going to sit?" He'll reply "at the table". Then we go back into the cafe bright and breezy, all upset forgotten and swiftly move on to something else: helping to pour water for everyone at the table with much praise, talking about things on the menu, or doing a sticker book together. It doesn't matter, just deal with the tantrum and move on swiftly.
Still, I could have carried him out I guess. I do try and distract him with items, talking about stuff but his concentration span is very short.
They all have a short concentration span. Try a little bag with toys that are easy to have at a cafe table: sheets of stickers and scraps of paper, little magnetic stick figure men, look & find books. Get him his own special wallet and fill it with a random assortment of coins, club cards, old gift cards and receipts etc. He can play about with those and pretend to pay for the bill.
Part of dealing with this behaviour is also about teaching him how to remain calm and occupied in places like cafes, doctors waiting rooms and so on.