In my opinion, I think it’s important to explain the why behind the need for a GROUP BY in your SQL when summing with OVER() clause and why you are getting repeated lines of data when you are expecting one row per BrandID. Take this example: You need to aggregate the total sale price of … Read more
The results of query methods can be limited via the keywords first or top, which can be used interchangeably. An optional numeric value can be appended to top/first to specify the maximum result size to be returned. If the number is left out, a result size of 1 is assumed. JpaClass findFirstByOrderByIdDesc(); referenced by Spring … Read more
If you just updated EntityFrameworkCore from version 2.x to 3.x and you’re running into this, change your using statement to Microsoft.Data.SqlClient instead of System.Data.SqlClient. If you’re using EntityFrameworkCore.SqlServer it already has that as a dependency, so you shouldn’t need to install it explicitly. This Microsoft blog explains the change. The Microsoft.Data.SqlClient package, … , will … Read more
I might not answer the “which one is best” question, but I can at least give you some insights and other examples to consider. First, you are talking about “generic API with content and a schema that can be user-defined”. That sound a lot like solr / elasticsearch which are both hi level wrappers over … Read more
The basic way, unfortunately, is to repeat yourself. SELECT CASE WHEN <condition 1> THEN <a1> WHEN <condition 2> THEN <a2> ELSE <a3> END, CASE WHEN <condition 1> THEN <b1> WHEN <condition 2> THEN <b2> ELSE <b3> END FROM <table> Fortunately, most RDBMS are clever enough to NOT have to evaluate the conditions multiple times. It’s … Read more
If I have a table with a field which is declared as accepting varchar(100) and then I actually insert the word “hello” how much real storage will be used on the mysql server? Mysql will store 5 bytes plus one byte for the length. If the varchar is greater than 255, then it will store … Read more
You could first do a self-join on the exchange rates which are ordered by date so that you have the start and the end date of each exchange rate, without any overlap or gap in the dates (maybe add that as view to your database – in my case I’m just using a common table … Read more
This is perfectly fine for CONSTRAINT CHECK. Just do this: Requirement: is it possible to create a constraint such that a column B can be null as long column A contains lets say ‘NEW’ but if the contents of column A changes to something else then column B is no longer allowed to be null? … Read more
As explained in the documentation, SERIAL is not a datatype, but a shortcut for a collection of other commands. So while you can’t change it simply by altering the type, you can achieve the same effect by running these other commands yourself: CREATE SEQUENCE temp_id_seq; ALTER TABLE temp ALTER COLUMN id SET NOT NULL; ALTER … Read more
I only know two ways. First is to use PREPARED STATEMENT (Example after PostgreSQL Manual): PREPARE usrrptplan (int) AS SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2; EXECUTE usrrptplan(1, current_date); PREPARE creates a prepared statement. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and … Read more