New answers tagged query
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 ...
- 136
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:
...
- 927
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(...
- 43
Top 50 recent answers are included