I have a field called Website with examples that look like: I am trying to use REGEXP_SUBSTR to isolate the domain: REGEXP_SUBSTR(“Website”, ‘[^https://]+’) Some of the results are working but others are not, for instance I am expecting cornstalk.com and penny.co but I am not receiving…
SQL Server – Retrieve list of month dates between two columns
I want to add a new column that should contain months between startdate & enddate present in two separate columns. My current data looks something like this : Case Name StartDate EndDate 1 ABC 2021-01-15 2021-03-15 2 DEF 2021-03-15 2021-05-15 My desired output is : Case Name StartDate EndDate MonthList 1 …
Gist index in PostgreSQL only works on order, but not on where predicate
I’ve got a plain table with LatLon column containing a point with object location in space with GiST index created on this column: I’ve got a query on selecting only points within some distance to the point with fixed coordinates: Although explain on such query shows the index is not being used du…
Slow Querying DB
I am currently optimising a system with many connected tables. The part that I am working on right now is displaying table orders. The problem is that in this table there are also many relations (around 10) which I am querying. The problem itself is in querying that many relations. I have been using Eloquent …
How to get data from sub-table?
i have “transaction” table and it has date_of_transaction, transaction_number, item_number columns. it has rows. also i have “item” sub-table. they linked with “item_number”. there are item_name and item_category columns for “item” table. i want to print rows wi…
How to make a composite key out of two foreign keys
I have three tables: In Table 1 primary key is pizza and in Table 2 ingredient. In Table 3, pizza and ingredient are foreign keys, that reference Table 1 and Table 2 respectively, and i want to make them into a composite key. Here’s what i tried: But i’m not sure whether it’s a correct imple…
SQL query to split and keep only the top N values
I have the following table data: I need to create a table with a split items column, but with the limitation to have at most N items per name. E.g. for N = 3 the table should look like this: I have the following query that splits items correctly, but doesn’t account for the maximum number N. What shoul…
How does foreign key resolve queries in terms of performance(indexing) on databases?
I’m wondering how queries with join operation perform when I have or don’t have a foreign key. For example, given the following schema: And given I have the following query: When I ask for the execution plan of it, it gives me the result below. It means the query is using the index I_EMP_DEPTID to…
Add information to one table from table contains duplicates
I have the following table: In Table_1, (ID, Name) pairs can repeat and have any combination Table_1: ID Name Value1 Value2 1 John 34 45 1 John 15 78 2 Randy 67 12 2 Randy 40 46 1 Randy 23 85 2 Holmes 10 100 I want to find all information for all unique pairs. So the output should be:
Removing rows based on a string in Snowflake (SQL)
Let’s say I had a table as follows: And what I want to do here is if an ID has a group and the same ID has the group-Direct, then the Direct row is removed. So what It should look like is: What happened? The only ones to change were ID = 001 and ID = 005. ID = 001