I have an audit table where entities are stored by id and an associated revision number and revision type (this is a Hibernate Envers audit table). E.g. id rev revtype foo_description 10 1 0 10 foo v1 10 3 1 10 foo v2 10 4 1 10 foo v3 20 2 0 20 foo v1 20 4 1 20 foo
Tag: sql
In sql how do you print only the most occuring
This actually tells us which person made the most purchases. But how can i print only the top row and not have all the other rows below? Answer You want to fetch the highest ranked row(s) of your intermediate result. If you want to keep your query as is and only add the appropriate clause, use FETCH and a mod…
Error: SQL Substring with the “ON” Part of the JOIN
I am using PostgreSQL and must join tables using substring. As I demonstrated below, xyz.MODEL and first three characters of “columnname” from abc Table should match. However, the query below does not work. It returns the error: Can anyone help me to correct this query? Thanks for your help alread…
Add class and onclick function to HTML string from SQL Server
I’ve a query string to create HTML row and the output is Is there any way to add an onclick function and class from SQL itself? Answer It’s unclear where you want the attributes to go, but you could do something like this db<>fiddle
Group elements by month three by three
I have this Standard SQL query: With the following output: You can simulate the output with: I am retreiving the entries of the last 12 months. I have already grouped and ordered them by month, but I need to also group them three by three according to the month. Moreover, as you can see in the output there ar…
Cannot connect to database stored on aws from Heroku or Replit websites. (mysql2, node.js)
Problem: My discord.js bot isn’t connecting to the mysql database on aws. The bot is being hosted on replit and also on heroku but for some reason I can’t connect to both. I have tried connecting to the database from my laptop and it worked fine, I even hosted the bot on my laptop to see if there …
Typeorm MINUS operator
I’m trying to do a set difference in SQL. I’ve discovered the MINUS operator, which seems to do exactly what I need: https://www.geeksforgeeks.org/sql-minus-operator/ I’m trying to figure out if there’s a way to use this operator via typeorm. I’m currently using connection.getRep…
join tables from different databases only adding most recent date SQL
I am joining lots of data to handle dynamic/complex querying. The following is just a part of what I’m doing. I found a solution, then I think I tweaked it and lost it. Database Structure: oauth.users order.orders ( I know bad naming, I didn’t create the database) (customer_id = user.id) I want to…
Mikro-ORM: error: there is no parameter $1
Alright so I’ve been trying to search Mikro-ORM’s docs to find how to pass in native sql query params in but I haven’t been able to find anything. After playing with the code a bit this is what it looks like which I think is sort of right but I’m getting this error My code currently lo…
What do I do to user data that is currently not verified?
Good day, I’m new to backend coding. What do I do to user data that did not confirm verification code from email during registration? Should I save it to database forever or do I have to delete it after a set of limited time when verified boolean is not met? I’m sorry if this is so beginner questi…