Good morning to everyone except my inability to stay asleep
COTW/ypercube/James and any other people familiar with London, this was an interesting pair of photos showing the development over 40 years twitter.com/jdpoc/status/1452759830456844290
While T-SQL scalar UDFs that are not inlined have historically been problematic sources of performance problems, there has been recent work from the engineering team to inline some UDFs to make this a non-issue for you. Here are the docs explaining the supported cases: SQL Documentation on UDF I...
Not a SQL Server man - what's wrong with the answer?
@McNets - I saw you take an interest in this earlier - as I suspected, we weren't getting the whole story! I'd be interested in your thoughts on my answer?
SELECT exam.test "Test", subject.name "Subject", exam.sub_code "Subject Code", exam.marks "Marks"
FROM exam
INNER JOIN subject ON exam.sub_code = subject.sub_code
WHERE roll_no=1
ORDER BY test,subject.sub_code;
@RandomPerson you could probably get somewhere with a case when test=lag(test) then '' else test end or something I'm not to good in MySQL syntax but @mustaccio is right you probably are trying to solve the wrong problem with the wrong tool here
@EvanCarroll - so that's what the C code stuff was about earlier? Fair play - but you may be straying outside of the parameters of the challenge - C code wasn't mentioned! :-)
That's 3 x 21ms and some change... I'll try running larger values of the params tomorrow - it's 20:00 here and I'm tired... I'd be interested in seeing any code though - have you posted it?
I'll edit and make the best one clear - I like the idea of showing different approaches - some of the alternatives may be appropriate for different use cases?
@EvanCarroll - done - I put the best two at the beginning - the best uses INTARRAY, the second standard PostgreSQL constructs with no extensions. HTH!
That said, if you "must" use this schema, I'd rather use a procedure for insert and another for update that take care of this order. In this example I've used triggers but I would prefer to use a procedure, it's more clear. You've done a 'lot of good work' to answer the question, but honestly I don't think OP appreciate it. dbfiddle.uk/…
In addition, I often feel these kind of ordering designs are often secondary to the actual data, and if so it should be overlaid from a separate table of ordering which can be very narrow, a doubly-linked list to insert an item, only two rows need to change to open the gap and link to the inserted row, all without affecting the actual entities. Or rewrite the entire sequence of items by delete and insert, again without affecting the entities' actual rows. But of course it depends.
We often use tables for linkages between entities even if they aren't many to many for reasons like this where the items don't change but the relationships do change. i.e. accounts might only be allowed to have a single parent account, but we might not store the parent account in the account as a foreign key but instead have some other organizing structure.
Or where the ordering of things is dependent on a particular user, then the structure table would be partitioned/keyed by the viewing user's preferences.