SQL - any and all

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!

I got this as a reaction to this post and I just felt it in my soul.

https://lemmy.world/comment/4795320

Jesus died at 33, so that can't get returned in the query response lol

As an RDBMS professional - I found this article quite engaging, although a bit old.

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.

Who doesn't love a good discussion about best practices? Here is an article from metabase on their recommended best practices for writing SQL. Share you opinion and recommendations.

Use SQL to query live data streams. Similar to Apache Fink and Kafka Streams.

New SQL 2023 - Per Peter, here are the main three topics:

1. Various smaller changes to the existing SQL language
2. New features related to JSON
3. A new part for property graph queries

Hi Everyone:

The SQL community has been created where we can discuss anything related to SQL, regardless of dialect or version.

Please share your posts and questions for the community to discuss or answer.