Ezequiel Tolnay
  • Member for 7 years, 1 month
  • Last seen this week
Compare two sets with a quantity field
Accepted answer
0 votes

You could create a query that first lists or groups elements as in your first example. Then select on that (as a subquery) grouping by basket and crating a string per basket through GROUP_CONCAT(). ...

View answer
Insert data into oracle table with loop?
0 votes

This is only judging from the very little information you provide: SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter, ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER ...

View answer
Count approximation with where clause
Accepted answer
0 votes

How many unique values do you have for crawled_name? How feasible it is that you'll hit an apartment_id value greater than 2 billion (in the next 5-10 years)? Note that COUNT(id) is the same as COUNT(...

View answer
Conditional relationships? One-to-many relationship using a pivot table for only a type of entity
Accepted answer
0 votes

In most RDBMSs you'll have the ability to set-up column conditionals, which are useful for cases like this. Say your user types are 1, 2 and 3 (res, admin, super respectively). You can set users this ...

View answer
can't connect to mysql via command line but workbench works, ssh tunneling required
0 votes

Your SSH session might be dying for lack of a terminal (the use of & launches it to the background and disconnects STDIN and STDOUT from it). Use two terminals, run SSH on one, and mysql on ...

View answer
Avoid repeated traversing of hierarchical data when parsing a big tree frequently
0 votes

You can simply use IN for XYZ, so that you don't need to have one for each relation): SELECT * FROM T1 LEFT JOIN T2 ON (T2.T1_ID = T1.ID) LEFT JOIN T3 ON (T3.T2_ID = T2.ID) LEFT JOIN T4 ON (T4.T3_ID =...

View answer
Postgres - Regroup rows with at least one common attribute
Accepted answer
0 votes

This was tricky but fun. Using a recursive query: with recursive v(l, f) as ( -- select Letter, Figure from table values ('A', 1), ('B', 1), ('B', 2), ('C', 2), ('C', 3), ('D', 3), ('E', 4)...

View answer
What is the performance impact of indexing a frequently updated column?
Accepted answer
0 votes

INSERTs have lower impact in affected indexes than UPDATEs, however INSERTs affect all indexes (except for conditional indexes that do not meet the condition). Indexes are necessary if you need to ...

View answer
Table of variable amount of skills for users
0 votes

If you can allow for several skills per employee, using a many-to-many table table (e.g. employees_skills) would be the proper way to normalise the data, however there are alternatives that may suit ...

View answer
MS sql query value one table but not another join table where*
0 votes

You can use NOT IN in such situations, or LEFT JOIN, as follows (not sure what you mean by [CourseID] in your select list, though): NOT IN This may not be efficient for very large numbers of ...

View answer
Trigram index for ILIKE patterns not working as expected
0 votes

The two analyses differ in the text comparison, the first shows ~~*, i.e. case insensitive (ILIKE) whilst the second shows only ~~, i.e. case sensitive (check if you didn't run LIKE by mistake the ...

View answer
Having two data folder in my postgresql setup- Postgresql
0 votes

On a terminal, type systemctl cat 'postgresql*' That will show you where the PGDATA environment variable is pointing to when you start the service normally via systemd, and all the parameters used ...

View answer
Complex MySQL query through relational tables
0 votes

@Serg's answer is effective and fast for a small set of attributes to compare, but it is limited to 30, i.e. hitting the maximum 61 tables that can be referenced in a query (MySQL 5.x). The following ...

View answer
Slow query / Indexes creation (PostgreSQL 9.2)
0 votes

Your strategy for getting information from full_path can be useful for a one-off, but for ongoing queries to it, especially over millions of records and expecting quick results, it is far from optimal....

View answer
Slow query / Indexes creation (PostgreSQL 9.2)
Accepted answer
0 votes

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs....

View answer
How to ensure that only unique row combinations are present?
Accepted answer
0 votes

You can use a deferred constraint trigger, or (what I'd do in this case) replace assignment table with an array column in set and a normal before update/insert per row trigger to sort the array, and ...

View answer
Display dates not in ascending for To_Char
Accepted answer
0 votes

If you sort by dates formatted in DD/MM/YYYY, then the sorting will put all the first of month first (of every month), then the seconds of month, etc. You can't sort by a column not included in a ...

View answer
What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?
0 votes

DROP SCHEMA would attempt obtaining exclusive use of the schema first, so it would only actually manage to drop the schema when PostgreSQL is done retrieving data from ongoing queries. Further queries ...

View answer
Optimizing PostgreSQL with Index
0 votes

For this particular query, the following index would work better: create index index1 on lineorder(lo_suppkey, lo_partkey, lo_orderdate); Or possibly better two separate indexes for supplier+partkey ...

View answer
Migrate SQL server data to new schema
0 votes

A possibly simple way is replacing all MS-Access tables with links to views in your SQL Server with the exact same structure as the old Access tables. If the views are simple enough (e.g. a select ...

View answer
Security of streaming replication?
0 votes

You can also use scp. Ensure that your local postgres user can ssh passwordlessly to your remote host (ssh-keygen -t dsa) and that its public key (~postgres/.ssh/id_dsa.pub) is included in the remote ...

View answer
Effectively searching through entire 1 level nested JSONB
0 votes

For large volumes of data, perhaps you can cut down on the cost of fulltext search by adding a fulltext GIN index, either for the whole json (to_tsvector(jsonb_column::text)) or the output of a ...

View answer
how to find the last date from the week number as input
0 votes

There's a function for that (NEXT_DAY): NEXT_DAY(DATE(CAST((<year>*1000 + (<week>-1)*7+1) AS CHAR(7)) -1, 'FRI') Thanks dnoeth for the tip on -1!

View answer
SQL Server -- More efficient alternatives to repeating an (almost) identical subquery
Accepted answer
0 votes

If you're selecting from tables that include any single-value key (indexed) that is unique for the result (e.g. table1.id), perhaps the following will perform better for your data: Where table1.id ...

View answer
How can I use MySQL variables in subqueries?
0 votes

You could do: SELECT * FROM animals WHERE CONCAT(',', @animal_names, ',') LIKE CONCAT('%,\'', name, '\',%'); It won't perform fast, but it'll work.

View answer
Query to sort through history table
0 votes

This will return all current TableA with previous Group info. Select *, (Select top 1 B From TableB Where started < b.started Order by started desc) as ...

View answer
column values do not return properly when column name called out specifically in select statement
0 votes

As @JonathanFite stated in the comments, date_utc is a system function, so your queries will seem to work when your field happens to have today's date, but will fail for any others (because it's not ...

View answer
Ignore empty rows while loading data into a table
0 votes

These are a couple of options to deal with this: Create the table with an auto_incremental id; or Delete the offending rows from the file prior to loading it (e.g. grep -v '^\( *,\)* *$' file > ...

View answer
Create Table Syntax
Accepted answer
0 votes

You're migrating SQL code intended for SQLServer. MySQL uses backticks instead of square brackets, and does not have user. The following code would be suitable for MySQL: CREATE TABLE `Test`.`Ite` ( `...

View answer
Top N Results Ordered by Joined Table Column
Accepted answer
0 votes

Your queries are not the same. The first one is required to perform a complete left join and then sort, in case the results of the left join render fewer records than the limit (in which case it would ...

View answer