No, the sequence number is a hint to miners; usually it is set to 255 (its maximum value). If not, you can sign a transaction where the signed input has sequence number 0, tweak it and increment to sequence number 1, tweak it again with sequence number 2, and so on. Each tweak requires a new signature, so the sequence number tells miners which of the conflicting transactions is the "real" one.
How useful this is, I don't know. You can't enforce miners respecting the sequence number, so any protocol depending on it can be trivially broken by colluding with miners. Maybe somebody will step in with a usecase, but I suspect this feature was not well-thought-out when it was introduced.
The ordering of inputs within a transaction is always fixed; if you reorder the inputs, then any non-SIGHASH_ANYONECANPAY inputs will need to be resigned.