Can the expected result in this case be accomplished without a subquery ? Maybe using a join ? We have a name say ‘jose’, Expected result is all rows which has same color as jose. Query should run in both MS-SQL and ORACLE. http://sqlfiddle.com/#!18/5f7e3/2 Answer You can get this result with a JO…
SQL extract only latest modified record AND correct days count IN 3 table join
This query needs to do 2 things it’s not doing at the moment: If a h.name is strictly equal to other h.name, it should only select the latest record (where the h.modify_at is the most recent). Currently it selects all “h”). The following line: (EXTRACT(epoch FROM (SELECT (NOW() – date_…
Sql Server – Update Statement is locking the table when executed immediately after Rollback Transaction
I have used transaction with try-catch block in my procedure. If any exception occurs, I am rolling back the transaction and after that trying to update a Results (Status) table. But, it looks like the Update statement is locking table (The Select query on that table is not working after that). Could you plea…
Are indexes on columns with always a different value worth it?
Does creating an index on a column that will always have a different value in each record (like a unique column) improves performances on SELECTs? I understand that having an index on a column named ie. status which can have 3 values (such as PENDING, DONE, FAILED) and searching only FAILED in 1kk records wil…
How to determine a percentage gain or loss for a series of people in a table in SQL
I’ve posted a few questions about a system I’m trying to write, and feel bad for asking so many questions. However, I’m still quite new (or revisiting from years ago) to the finer elements of SQL …
SQL Server How to get number of unique values in a column and average score per values?
I have a table like this and so on What I ve done I got distinct count for every city and avg metric value But it is false Appreciate any help updated There is also an additional column id in varchar format Answer The answer here depends on this assumption: You always have exactly 3 metrics per ‘group&#…
I wonder using INNER JOIN and equality operator is faster or using IN when I try to filter data from a table by another table’s column
While I’m trying to get all accounts, which received message from foo@gmail.com. I want to know about the performance of two following query. or Thanks! Answer Always use the first query in such situations. Don’t use DISTINCT inside an IN clause subquery, though. Don’t try to tell the DBMS h…
SQL – Select using attributes from two tables
I am having an issue in extracting data using data of two tables in SQL. This returns A, B, C, D where E in T2 is not ZZZ. However, when I add another where clause like below, it returns data where T2 is ZZZ also. This ignores “T2.E <> ‘ZZZ’” part, but “D<>0” is not i…
Redshift SQL: add and reset a counter with date and group considered
Suppose I have a table below. I’d like to have a counter to count the # of times when a Customer (there are many) is in Segment A. If the Customer jumps to a different Segment between 2 quarters, the …
How to Use a Subquery in MySQL in the Where clause that is an argument to an OR operator?
I am trying to filter a table for specific markets by using a subquery. I want to include all null values and exclude the markets that are included in the subquery. This query isn’t filtering out the markets I want to get rid of. And this created a SQL compilation error. When I list the markets I want t…