All Questions
Tagged with subquery sql-server
118 questions
0
votes
1
answer
42
views
Add another subquery to one with a pivot
Obviously I need to study a lot more.
Erik Darling helped me with a great query to pull latest backup information (below) and Pivot on full and incremental. I wanted to add infor4mation if the backup ...
- 33
0
votes
1
answer
42
views
Display in asc order from multiple databases under multiple linked server and add two date parameters so that I can integrate it to my app
I have a script below where it display a result from different databases under different linked Server. The query is just connected or linked thru union all and it seems there's no issue on its result....
2
votes
3
answers
682
views
How can I get an average from a query dataset in SQL Server?
I am building a parts inventory system, and have a transaction table that stores how often an item in used.
Let's say Item A is used 10 times in 2021, 20 in 2022, and 30 in 2023. Of course each ...
- 23
0
votes
1
answer
65
views
Why the following first of two queries in MSSQL is faster as I want the second one?
1st Query: result 32 seconds (1,758,220 rows)
SELECT
CAST(ID.PYYY AS varchar(8))+','+
CAST(ID.SYYY AS varchar(8))+','+
(
SELECT TOP 1 (
CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+
CAST(...
- 101
0
votes
1
answer
299
views
Doesn't T-SQL support correlated CROSS JOINs?
This query works
SELECT QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM SWOPS.MIGR.QMCAddresses QMC
CROSS JOIN (SELECT STRNAME FROM SWOPS.MIGR.EB_DICT_STREET_QMC WHERE FK=8055909) t1
CROSS JOIN ...
0
votes
1
answer
628
views
How to map the newly created records in parent table with the child table?
have a table
Employees
Id Name EmployeeType_ID
1 John 1
2. Mathw 2
EmployeeTypes
Id TypeName
1 Perm
2. Temp
3. Regular
Now I am copying data from one server to ...
1
vote
1
answer
2k
views
How do I get the sum of a field and avoid duplication from an inner join?
I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%".
Accounts are unique, and there can be many ...
- 13
1
vote
1
answer
1k
views
SQL Main query value used in the WHERE in the subquery
I'm really confused by this SQL here, I have this sample where I am using the value from the main query in the subquery and then using XML PATH to add a comma between the dates. The setup is 2 tables ...
- 155
0
votes
1
answer
159
views
How can I improve the run time of a query that groups / display same date data in one row?
I currently have a view that contains about 1k rows of user information. currently i have a query that groups all the same user information by date into one row. Currently this query takes between 20 ...
- 17
0
votes
1
answer
769
views
Understanding why getting each parent row joined with its latest child is not working when using subquery filter
I have a parent-children tables relationship, and I was trying to get each parent row with its latest child row details.
My attempts to do that using a sub-query didn't work, and I could only made ...
- 457
0
votes
1
answer
798
views
SQL Insert by subquery inside update query [closed]
I want to Write a single query that updates columns in one table and insert rows in another table.
something like this
update
applications
set
applications....
- 13
0
votes
1
answer
663
views
Group by bit column
I want Group by rows with active column like result
I tray with this code and I Do not get result
WITH CTE as (
SELECT
RN = ROW_NUMBER() OVER (ORDER BY id),
*
FROM test
)
SELECT
...
0
votes
1
answer
1k
views
use row_number() over(partition by col order by col) in where clause [closed]
I have these tables in which I need to use Row_number() in where clause like this
where spectrumcms.staffchilddiscountpolicy.childno =
(select ROW_NUMBER() Over (Partition by std.GuardianCNIC order ...
3
votes
1
answer
1k
views
Understanding the relationship between a Table Spool and correlated subquery
I'm learning about execution plans, and have a question regarding the relationship between a Table Spool and correlated subquery (I'm following this tutorial, don't mind the typos).
I've created the ...
- 457
0
votes
2
answers
698
views
Subqueries in SQL Server
I am taking a SQL course and am completely stumped on this question.
Using a subquery have a query that returns:
CustomerID, EmailAddress, FirstName, LastName, '#3: Customers who have ordered than 1 ...
0
votes
1
answer
3k
views
SQL sql subquery with SUM for totals
I am a little stuck on getting a total value for a column using the SUM function. I want to get the total amount of items for an order from my database. The DB order is made up of several line items ...
- 1
1
vote
0
answers
131
views
Ranking and use of cube by in an SQL query? [closed]
I am a the MSc student who does not understand this following SQL dilemma and want to be certain from an Academic standpoint.
The entire “Foodmart” database can be downloaded from: http://didawiki.cli....
- 11
-3
votes
1
answer
297
views
Change Consecutive Row Value [closed]
I am trying to change value of Consecutive AA to first AA as AA1 and second one as AA2, if only one AA then it should be as AA1, its possible to change all AA to AA1 and then search for 2nd ...
1
vote
1
answer
585
views
Getting the "flat" query corresponding to a view using nested views?
I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
- 111
1
vote
2
answers
2k
views
Mssql subquery inside a substring
I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it.
The subselect collects all rows and converts them ...
8
votes
1
answer
6k
views
Why is SQL Server spliting it's (JSON) response into multiple rows?
I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
- 311
0
votes
1
answer
815
views
Sql Group by on subquery rows
I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :
DateArret ||HeureDebut ||HeureFin ||EnumArret
2020-11-30 ||2020-11-...
-4
votes
1
answer
778
views
Error when running query as a subquery [closed]
The inner query runs successfully alone, but when I run it as a subquery, an error pop up.
My query:
Select TOP 3 *
FROM (select
fr.rating
, sp.SpId
,sp.FName
,sp....
0
votes
2
answers
53
views
Optimize duplicated but negated subquery
I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
(b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
- 101
1
vote
1
answer
995
views
T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?
What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.
How should the query look like to achieve this, since the AVG function is an ...
- 151
2
votes
1
answer
5k
views
Sql Server string concat with order by and subquery
While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a ...
0
votes
1
answer
263
views
Select rows from table having specific values depending on another values in the same table
I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example:
Userpin TrDate Time1 Tr_type
4555 12-02-2020 ...
- 3
0
votes
1
answer
2k
views
Will subquery (using IN) run multiple times for update statement (SQL Server 2017)
Where an update statement is updating the value returned by a subquery - will the subquery be run for each row?
UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA ...
- 3
0
votes
4
answers
102
views
How to visualize joins between inner queries to their respective outer queries?
I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.
Here's an example:
SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE ...
- 39.5k
0
votes
1
answer
635
views
How to show multiple record if has top 1
Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
- 29
0
votes
2
answers
31k
views
Running a query on the results of another query?
I have 2 queries, they both run fine and pull the results I'm looking for. However, I need to be able to run one query from the results of another. This is t-sql/mssql/sybase
For example, the ...
- 153
-1
votes
1
answer
2k
views
Aggregate function on subquery
I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order:
Offer -> Order -> Delivery -> Invoice.
For each new row their ...
- 1
8
votes
1
answer
4k
views
Replace long GROUP BY list with a subquery
This is a repost of my question on Stack Overflow. They suggested to ask it here:
I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should ...
- 193
3
votes
2
answers
204
views
Can subqueries produce different execution plans than CTE's?
I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test:
...
- 2,650
0
votes
1
answer
2k
views
Query to get the physical file name of the database
Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\...
- 617
0
votes
1
answer
1k
views
Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value
I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each day is divided into 24hrs/2 = 48. ...
- 181
0
votes
1
answer
45
views
Slecting specific query returnned data related records from another table [closed]
Product table:
productId | productName | unitPrice | supplierId
_______________________________________________
1 | Iphone8 | 1500 | 3
2 | Nokia 6 | 800 | 1
...
- 37
5
votes
1
answer
10k
views
How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?
It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too.
In the following, I have a query ...
- 279
0
votes
1
answer
47
views
Two aggregated sub queries wont run
I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? ...
- 35
3
votes
2
answers
75
views
What am I doing wrong with subquery
I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query?
Question: Show ...
- 35
3
votes
1
answer
2k
views
Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?
I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
- 1,645
2
votes
3
answers
8k
views
Return ALL rows based on a matching date also matching a specific value in another column
I'm using SQL Server 2014, exact version: 12.0.5579.0.
I've tried a subquery under a SELECT and WHERE statement and it brings back false results with rows of any row with a 1/1/2019 date. It just ...
- 23
-3
votes
1
answer
284
views
Print Two Different Columns with Union All- SQL
I have an SQL statement like this,
SELECT *
FROM (select "John" as FirstName Union All select "Mary" Union All select "Alis"),
(select "Doe" as LastName union all select "Queen" union all ...
- 107
0
votes
1
answer
6k
views
Multicondition Subquery Max Date and Item Number
The following is a query to get latest Sales date for a customer.
select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
select max(SalesDate)
from ...
user173948
0
votes
1
answer
516
views
Using a Calculated Field in a Separate Calculated Field
I'm trying to avoid creating a temp table for just one single field, but I need to use a calculated field in a separate calculated field. For example:
,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
...
- 189
-1
votes
1
answer
2k
views
How to do group by based on another column in SELECT? [closed]
I have a table on which I do a SELECT statement. It is joined to some other tables. With the SELECT one column is got but a sub query. like (select top 1 .. from..) for each row. Now I need to do an ...
- 2,142
3
votes
1
answer
223
views
Correlated Subquery Versus Non-Correlated Subquery
There are three tables that may be used by a business to keep track of the absent days of its employees. It's from a collection of SQL puzzles by Joe Celko. The reader is tasked with concocting a ...
-4
votes
1
answer
354
views
Selecting data from a sub query
I am doing the exercises this is the link (https://www.wiseowl.co.uk/sql/exercises/standard/subqueries/4228/).
Can anyone help me. Thanks in advance!
Question
Write a SELECT statement to return events ...
0
votes
4
answers
4k
views
filter out the records with same id having different values in a column
Can anyone help me to solution for below issue.
create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,...
- 129
0
votes
1
answer
102
views
Finding the last record within three years of the first record with subqueries [closed]
I have the following table with student enrollment data:
CREATE TABLE [dbo].[Enrollments](
[SequenceID] [bigint] NULL,
[SchoolYear] [int] NULL,
[StudentID] [varchar](8) NULL,
[...
- 111