New answers tagged

2 votes
Accepted

Varbinary update attempt

As I read the question, you want to replace the leftmost occurrence of three zero bytes with 0xFFFFFF: DECLARE @ToFind binary(3) = 0x000000, @ReplaceWith binary(3) = 0xFFFFFF; UPDATE YT SET ...
  • 78.5k
3 votes

Varbinary update attempt

You can do this by running through every possible substring in order until you find a match. Then piece together the new binary value using SUBSTRING. I have used a Nums function to generate the ...
  • 8,806
1 vote
Accepted

Query to select field only if two values and count is satisfied

You can use a windowed count to check for at least two rows per set. Then use TOP 1 WITH TIES to get the first set. SELECT TOP (1) WITH TIES ID, Context, StartedAt, FinishedAt FROM ( ...
  • 8,806
1 vote
Accepted

Selecting second highest value using two SQL queries returns different results

These queries are fundamentally different. One has a scalar aggregation, and one does not. This query is selecting the first row which is not the maximum. If there is no row then no results will be ...
  • 8,806
2 votes
Accepted

Oracle SQL: Create a new field based on multiplying 2 columns

I need to create a new column lets say Amount_CNY that represent each amount, but exchanged to "Yuan (CNY)". So this "new" column resultant of the existing columns The thing is ...
  • 2,999
1 vote

Ask SQL Server to run a query but return results when i ask for results

This sounds like Sql Server Reporting Services (SSRS). It can run the query ahead of time; cache the results for the users to retrieve later.
2 votes
Accepted

Pulling the postcode area from the full postcode

You should be able to use the same regex in MariaDB as you did in the JavaScript by using the REGEXP_SUBSTR function, like: SELECT REGEXP_SUBSTR(Postcode, '^[A-Z]+') AS PostalArea From ...
0 votes

Complicated query that compares subselects

So I managed to get the hardcoded numbers from a subquery with join, but I'm not sure if this is correct. I am not sure about this subquery, if it is correctly joined: -- ... SELECT ...
  • 123
1 vote
Accepted

How To turn a string with "pipe-separated" values into individual rows in Oracle PL/SQL

You simply need to add in additional columns, e.g.: WITH student_info AS (SELECT 1 item_number, 'Ali|Reza|Pantea' st_firstname, 'Hashemi|Nosrati|Yaghobi' st_lastname, '10|20|20' st_score FROM dual ...
  • 220
1 vote

Complicated query that compares subselects

If you think of a Venn diagram, you are fetching the items that in one table and not the other? I see no advantage, and a significant performance disadvantage, in having that query the extra layer of ...
  • 73.8k
2 votes

Re-write a query in SQL Server 2012

I would start with getting a list of the IDs of the products whose only version is productType = 'holder' and is mandatory This is how I would go about it: SELECT productId FROM #productVersion ...
  • 22.3k
1 vote

Multiple records same id retrieval

Another option might be conditional aggregation (without it, result would be displayed in several rows) along with EXISTS which filters out users who don't have EMAIL as a contact option. Sample data: ...
4 votes
Accepted

Multiple records same id retrieval

In your case, what you need is a self-join: SELECT contact_email.USERID ,contact_email.VALUE AS EMAIL ,contact_mobile.VALUE AS MOBILE FROM contacts AS contact_email LEFT OUTER JOIN ...
  • 414
0 votes

Calculating a percentage based on Group

You can achieve this with the help of window aggregation: SELECT Type , SUM(COALESCE(Expense, 0) + COALESCE(Income, 0)) * 100 / SUM(SUM(COALESCE(Expense, 0) + COALESCE(Income, 0))) OVER () AS ...
  • 22.3k
0 votes

Calculating a percentage based on Group

I think I was making this more complicated than it needed to be. I managed to figure out the correct query, which is far simpler than the one above. SELECT Date, Type, (SUM(IncExp) / (SELECT SUM(...

Top 50 recent answers are included