I had an interesting conversation at work where someone questioned my use of DENSE_RANK and suggested I use LAST_VALUE. We had a good conversation about it, but ultimately agreed to disagree about the specific scenario. My code was accurate with no performance issues warranting a rewrite. My feeling was the person I was talking too was less familiar with DENSE_RANK and so favored LAST_VALUE. I get preference, I have my own, but I try to be aware of what's preference vs. what's technically superior.

I'm curious about people's thoughts on when to use LAST_VALUE() vs MAX() KEEP(DENSE_RANK LAST ORDER BY ). To my mind, both solve a similar problem. I lean towards DENSE_RANK largely out of preference due to the syntax being shorter (literally just less characters to type), and I also like that since DENSE_RANK is an aggregate function it outputs distinct results without needing to use the DISTINCT keyword. I haven't intentionally run tests comparing the two, but anecdotally I've never noticed a performance difference between the two when writing comparable queries.

Surprisingly I couldn't really find any detailed articles or discussions online comparing these two functions and I'm curious what thoughts are out there in the wild.

I currently run operations for a small lab. I have built a fairly complex Access database from scratch in order to manage all budgeting, purchasing, inventory, and invoicing systems. I spend a bit of time each day maintaining/improving it. I am also currently in a year long computer science program learning c++. I would like more practice with sql was just curious if anyone had any recommendations for recreating my database in a more modern fashion.. Access works, but is a bit clunky/fragile.

Our software budget is nonexistent, so considering open source or from scratch. I have recreated all my tables in mysql, but is mysql the best way to go? As far as a front end, is there anything I can implement with c++? Or is this worth dabbling in another language for? I want something that I can both learn from but will also be useful/reliable enough to replace my Access database. Or would this be a total waste of time for how challenging building this from scratch would be?

Thanks! I appreciate any advice!

Hi!

I'm looking at manually entering quite a bit of relational data and I am wondering whether anyone knows of a nice GUI tool to do it?

What I'm looking at doing is something like matching up names with addresses. I have a table with a list of people, and I have a list of properties. I want to create a table where I could pair up people with properties. I would like the ID's to be entered into the Database, but I would like to see names and addresses on the front-end. Preferably when I click into the new relational table's columns it would give me an auto-complete list of possible values as I type. (So offer suggestions for names / addresses).

I'm looking for something web-based or MacOS / Linux compatible.

Does anyone know of such a tool?

I recently tried my hand at an advanced online SQL test for a io position. Time-trial, non-autocompleting IDE, no human interaction. I failed miserably (0%) and I am fine with it. I have prepared myself with StrataScratch and the Mode tutorial, on top of my experience in querying GA4 data in BQ and other data for finance, sales and operations. . Although the resources are good to familiarize with SQL functions and structuring queries, I still feel that I am missing some kind of mindset that would enable me to tackle SQL questions irrespective of complexity. I don't want to know necessarily the details, if it is hard for you to explain, but I would like to know if there is such a state of mind to achieve. Of course, any description and resource is welcome.

Obviously Microsoft Access is the best but it would be cool to hear your opinion.