I have two really long selects that are both nested How can I order these (both queries combined into one table results) by NewestMessage which is a type of datetime? Answer I would slightly alter your query to: I am basically replacing your union of tuples to a subquery on a union query, using an ORDER BY wi…
How to filter the record based on two columns in sql
i have a table with 2 columns and i wanted to filter the records where column1 in (Hierarchy,Single) and column2 in (‘Relate’, ‘NoRelate’). but not return the record when Column1 = Single and Column2 =…
Regexp search SQL query fields
I have a repository of SQL queries and I want to understand which queries use certain tables or fields. Let’s say I want to understand what queries use the email field, how can I write it? Example SQL …
SQL Remove all leading zeros
My join returns null because I am not able to remove the leading zeros. Basically, I am trying to replace the pol.Policy_Number with src.Policy_Number for a large number of policies. Is there a way I can remove the leading 0s in the ON statement for src.Policy_Number? I think Padding is needed, but I am new t…
PostgreSQL — is it possible to use named window in query’s ORDER BY clause?
I’m trying to do something along the lines of SELECT id, sum(weight) OVER w FROM foo — Use random() to break ties WINDOW w AS (ORDER BY weight DESC, random()) ORDER BY w LIMIT 1; but it errors out …
get aggregation values by other values in sql
I have two tables that looks like this in their schema : create table PRODUCT_NOMENCLATURE(product_id integer, product_type varchar(100), product_name varchar(100)); insert into PRODUCT_NOMENCLATURE(…
How does page overflow with columns (InnoDB) work in MySQL?
The documentation says: “Whether columns are stored off-page depends on the page size and the total size of the row.” 1.- This means that if I have a page size of 16KB, the maximum size of the row would be 8KB, therefore, if I have 4 columns, will the maximum size of each column be 2KB (approximat…
SQL: how do you calculate occupancy/ number of in-use for a facility?
I am trying to calculate the number of occupied lockers in the changing room. The dataset goes like this: My ideal output will be the number of occupied lockers per hour: I am able to manually calculate the number in excel (‘no. of check-in lockers during the hour’ + ‘no. of lockers in-use d…
addition within sum case when
How to sum all the customers in DS01,02,03 into DS? select sum(case when Ticket=’DS01′ OR ‘DSO2’ OR ‘DS03′ THEN customer ELSE 0 END) AS DS sum(case when Ticket=’MSO2’ OR ‘MS03’ THEN customer ELSE 0 END) AS MS from table group by ticket unsure how to …
SQL Server How to UPDATE with SELECT and GROUP BY clause
Our team uses SQL Server and I wanted to use aggregate function to update one of the columns for the NEW values that are being inserted existing table. Below is the existing table and values in it. …