@EvanCarroll I look forward to learning from your high-quality input!

Morning

Morning

Good morning.

A chairde - Morning all!

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

@billinkc interesting. thanks.

@McNets then, Crystal Reports although "prefer" is a bit strong.

Like this joker knows anything about how SQL Server works...

@HannahVernon 👍

@HannahVernon In fact I think I'll use SSRS. I can get a PDF w/o adding any viewer to my code.

I leave the reporting layer to the peasants

Oh sorry, I mean BI devs

So easy to get mixed up

lol

...and closed.

Has bluefeet had been gone left yet?

@billinkc What's a "Partner Architect"?

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?

@Vérace give me some time, I must update a web server certificate that expires just today

@McNets - take all the time you want el meu amic!

@Vérace 1. depends heavily on version - I think inlining didn't happen until 2019. 2. There are a lot of "gotchas" when the function won't inline

@Vérace Not sure what that title is, but he's like one of the heads of the SQL Server team for MS

@billinkc So this: Like this joker knows anything about how SQL Server works... was itself a joke/sarcasm? I feel like Data in Star Trek! :-)

@Vérace Definitely sarcasm, but I'd say that answer isn't barely above link-only

*is

Hey people!

I wanna know if it's possible to something in MySQL.

This is the output of my query:

+------+-----------------------+--------------+-------+
| Test | Subject               | Subject Code | Marks |
+------+-----------------------+--------------+-------+
| MT1  | Maths                 | 041          |    29 |
| MT1  | Physics               | 042          |    36 |
| MT1  | Chemistry             | 043          |    42 |
| MT1  | Informatics Practices | 065          |    46 |
| MT1  | English               | 301          |    38 |
| MT2  | Maths                 | 041          |    37 |

Can I format the output to something like this:

+------+-----------------------+--------------+-------+
| Test | Subject               | Subject Code | Marks |
+------+-----------------------+--------------+-------+
| MT1  | Maths                 | 041          |    29 |
|      | Physics               | 042          |    36 |
|      | Chemistry             | 043          |    42 |
|      | Informatics Practices | 065          |    46 |
|      | English               | 301          |    38 |
+------+-----------------------+--------------+-------+

@RandomPerson BTW, this is my query:

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 Hey person! MySQL is not a formatting tool, you should use what's appropriate for the job.

@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

@mustaccio OK. Thank you.

@TomV ok.. Thank you.

@Vérace I will win your benchmark today.

@Vérace I wrote some code last night, worked out well.

@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! :-)

It wasn't not mentioned.

True - nope, it wasn't. As I said previously, if I learn something, I consider it a win!

You should also add a new line in the benchmark params for 10000,10000 =)

How long does that take you (please also provide machine specs CPU, RAM, disk)?

ecarroll=# EXPLAIN ANALYZE SELECT * FROM generate_up_down_series(10000,10000);
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_up_down_series  (cost=0.00..10.00 rows=1000 width=4) (actual time=12892.537..24681.338 rows=200000000 loops=1)
 Planning Time: 0.054 ms
 Execution Time: 29093.186 ms
(3 rows)

Intel(R) Core(TM) i7-10710U CPU @ 1.10GHz

(hyperthreading off) 16gb of ram ssd

I mean, the fun thing about a 10k/10k challange isn't that I could just do it, it's that I would write one that was multithreaded. ;)

I mean, you could also copy the in-memory tuples

since the m value is just a duplicate memory block

That's not so hot! I make it 20,000,000 / 387,000 = 516 - 29/516 = .29 - therefore, I still claim victory - I did the equivalent in .21 - per element!

:-)

Unless my calculations are wrong in some way?

?

Where is your code?

Oh you mean using intarray instead of using a resultset?

@EvanCarroll I'm not sure what you mean by that?

I don't understand what you're numbers mean there 20,000,000 / 387,000 = 516 - 29/516 = .29 - therefore,

@EvanCarroll How long does your solution take using 340,570 as parameters?

ecarroll=# EXPLAIN ANALYZE SELECT * FROM generate_up_down_series(340,570);
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_up_down_series  (cost=0.00..10.00 rows=1000 width=4) (actual time=35.627..59.446 rows=387600 loops=1)
 Planning Time: 0.056 ms
 Execution Time: 68.741 ms
(3 rows)

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?

No, but I will do today?

what solution got 21ms?

@EvanCarroll Mine - check out my answer - my best effort is now 21ms!

can you clean answer to just include your best.

god that's a lot of text

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!

@Vérace to me, renumbering a UNIQUE or PK means a bad design, sooner or later it means "problem". If you need an specific order, add a column for that purpose and maybe a NONCLUSTERED INDEX depending on the size of the table. SELECT foo, bar FROM myTable ORDER BY myOrderColumn.

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.

twitter.com/TheEvanCarroll/status/1453850491516071944