Tag Info

The answer is because null is treated as false-y in a WHERE clause. You can test this out by comparing: SELECT 1 WHERE true; -- vs SELECT 1 WHERE null; You have a null occurring in the WHERE clause ...

None of the above will work if you can have a unique column in table to identify which one to keep. In case of true duplicate, you will need to extract duplicate records to a temp table delete all ...

Thanks! That's really the answer.

You can use COUNT() and CASE. Simply don't define a value for your ELSE condition since NULL values are skipped when using COUNT(). COUNT(CASE WHEN <condition> THEN 1 END) In your specific case:...

Another version of selecting a current date: SELECT Date, Content FROM MYDATABASE WHERE Date LIKE '%-07-15'; More details in SQLite documentation.

You forgot to add you function signature - without that, "paired" arguments can mean a number of things. It helps to underline you're only looking for alternate ways to invoke a function, ...

The column TrxnDate in your table is a misnomer and doesn't contain dates, but datetimes / timestamps. Now you want to select all rows falling in a particular date range and additionally in a ...

You can use a join to a list of date values, plus some calculation to add the hours. declare @startHour int = 0, @endHour int = 6; select TrxnDate, UserName, UserType from (values (CAST('...

You can do it by using Hibernate API. // given EntityManager em instance Session s = em.unwrap(Session.class); NativeQuery<Building> q = s.createNativeQuery(""" SELECT {b....

Thanks! But the task is to not change the function.

Instead of scanning the table t twice, it might be better to collect an array of pairs with a single subquery. Here is a complete example. We need a composite data type to represent a pair: CREATE ...

In 2026, the official way to see an object's owner is : DESCRIBE <securable-type> EXTENDED <catalog>.<schema>.<securable-name>; and with no special privileges: Permissions ...

The code below makes a couple of changes to your code. First, you need to enumerate the source data, so that you can process one row at a time. I used ROW_NUMBER for that. Second, the recursive branch'...

Firebase (Firestore/Realtime Database) is NoSQL — there's no built-in SQL mode. But you can query your Firestore data with real SQL using fire_duck_ext, a DuckDB community extension that connects ...

You are missing a join condition between schedules_tb.user_id and users_tb.id. Also this function could probably be a normal sql function rather than pgpsql, this will allow for inlining. You should ...

This is an old request, but well, I thought I'd just add an answer, for future readers may benefit from this. Unfortunately, the request shows a mutilated query instead of the real one. Those who can ...

This doesn't actually need any kind of recursion, you just need a couple of window functions. ROW_NUMBER will count the rows, then we subtract one to get a zero-based start. We also need LEAD(1) so we ...

Think about how you would learn chords of a particular song on guitar. How would you do it? You'd find a guitar tab reference, and study it. Then you'd play it, probably not the whole song at first, ...

If you use something occasionally then it's impossible to remember all details. If you use something few times a day then brain can remember it automatically.

Same. I've been using MySQL for 25 years, and I still refer to documentation.

One public repo per project, with a top level README.md thoroughly describing it. You can also have README.md files in directories. Link to other projects where there's some relation.

In addition to checking documentation, I like to create a Markdown file with ways to solve problems, or code snippets. Creating a GitHub Gist works too.

This is what documentation is for. I've been using MySQL for over a decade and I still consult the documentation occasionally to get the function names and argument order correct.

GitHub just hosts code repositories. Step one is to have something to show. Maybe you're thinking of GitHub pages? It is meant to host documentation, but you can use a static site generator to create ...

My expectation is that the join would yield exactly as many rows as table1 without the join. The join would just bring in one more column (fieldX) This expectation is incorrect. Rows for table1 are ...

I would suggest to try it: create an index on (start_date, expiry_date) create one on (expiry_date, start_date) check which index gets used and when what happens, if you search for very young entries, ...

I am planning to make my backend api query a user's privileges from the users_permissions only and check for is_granted ( role_permissions table are only for seeding the default permissions in ...

In this case you can use both a ROOT parameter as well as a column alias. SELECT id, name, (SELECT class_name FROM classes c WHERE c.student_id = s.id FOR XML PATH('...

Please try the following solution. You just need to add one more level to the XML hierarchy. XML is case-sensitive. Your T-SQL element names are not matching the desired output. For example: PATH('...

How is this relevant to the question asked?

Then what happens if the permissions assigned to a role are changed some time later?

my challenge and thinking is that whenever I make a change to either the receipt number, date or amount in my PT using the y_trans_code as my where statement this should affect the entire row in the ...

FYI, see the attached hyperlink for a more comprehensive and abstract explanation of the issue. https://en.wikipedia.org/wiki/Hilbert_scheme?wprov=sfla1

Regarding your point no 1, I am thinking of querying a user's privileges from the users_permissions only and check for is_granted . ( role_permissions are only for seeding the default permissions in ...

Your design models per-user overrides, with caveats: A given user's privileges are the union of those granted by roles and those granted as overrides. To query the user's privileges, you'll have to ...

Maybe my suggest will help someone. If you are using liquibase then you have to specify for sql script option splitStatements to disable split statement by semicolons. In your changelog.xml file it ...

I just had to solve a similar problem but my years where wildly distributed, ranging from 2030 to 4077, which I wanted to reset to 1930 and 1977 respectively while keeping day and month. So I ...

One thing developers must keep in mind is that SQL is not a procedural language. It's a calculus: The engine will compile your query and, based on heuristics, try to plan the most efficient way to ...

If expiry_date is the more selective of the two dates, as you describe, then make it the first column in the index. The query, however, stays the same. It doesn't matter how you write the query, the ...

I'd expect DBMS engine to benefit from fast return for the case when expiry_date is checked first, for it's more likely to return false in case the period between start_date and expiry_date is ...

For the case that the range itself is selective a technique that can be used to work around this is https://dba.stackexchange.com/questions/14894/optimizing-ip-range-search/14896#14896

If you'd look at The json_array() function docs, in the examples you could see that it returns text with serialized JSON, not an object. So if we were to put it as table result you would get something ...

(Edited: I had my answer backwards. Also added demo fiddle that compares index reads.) I've always considered the inability to directly support ranges as a shortcoming of SQL Server (and likely most ...

I have updated the post @Sergey

I have updated the thread with more detail @Laurenz

I think you might be talking about an ALIAS? ...SELECT WOTEMPLATEID AS DESCRIPTION,.....

So whats stopping you? What help are you asking for? Just add it? And which RDBMS? Following is for SQL Server https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-view-transact-sql?view=sql-...

Haven't seen this answer yet: subquery with LIMIT 1. SELECT any_userdata FROM user WHERE (SELECT anything FROM whatever WHERE rating > 10 LIMIT 1) IS NOT NULL

I don't understand "should I replace my original query with the one relying on fast return for expiry_date?". How would you write a query that relies on "a fast return for expiry_date&...

I am not a SQL Server developer, so I only can give general advice. An index will only be used when it makes sense to the DBMS. It won't use an index, if it expects to have to read half the table ...