Insert Into #Temp Values('Karan')
Insert Into #Temp Values('Gupta')
Insert Into #Temp Values('Karan')
Insert Into #Temp Values('Karan')
Insert Into #TempAddress Values(1,'Address1')
Insert Into #TempAddress Values(2,'Address2')
Insert Into #TempAddress Values(3,'Address3')
select PK_ID from #Temp a where (select PK_ID from #TempAddress b where a.PK_ID = b.PersonID)
Drop Table #Temp
Drop Table #TempAddress
But I am getting an error
Msg 4145, Level 15, State 1, Line 29
An expression of non-boolean type specified in a context where a condition is expected, near 'Drop'.
Am i doing something wrong.
Kindly advice
Regards
Karan
I need to come up with a query (I really don't want to use cursors) that will perform the following:
I have a message table that houses multiple users with multiple messages...for every user, I need to delete every message after the 20th oldest message.
Here are the pertinent fields: userid,message_id,message_date
This really a question of approach more than anything else.
The situation is as follows:
I have a set of data that should contain one row for every company for every date in a supplied date range. Any companies that do not have a row for all dates, or have null values in certain required fields, should be dropped from the series all together.
So that...Given the date range 11/1/2007 to 11/2/2007 and the data set
Only the two rows for CompanyB would ultimately be delivered.
On a tip I looked into correlated subqueries but that doesn't seem to fit. Does anyone else have an approach that might be best for a situation like this?
Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table? Above is the original question.
My understanding below I think two tables sales and titles. The title_name, title_id is in the titles table. Quantity is in the sales table. My question is, how can i write a correlated subquery to show titles that have sales?Any feed back is would be thankfull.
I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!
select * from st_Meta_Field MF INNER JOIN st_field F ON MF.Field_ID = F.Field_ID where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY') AND F.Record_State = 1 AND MF.Record_State = 1
select max(display_row),subset_value from st_Meta_Field where table_id = 5 AND Display_Row < 500 group by Subset_Value
I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!
select * from st_Meta_Field MF INNER JOIN st_field F ON MF.Field_ID = F.Field_ID where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY') AND F.Record_State = 1 AND MF.Record_State = 1
select max(display_row),subset_value from st_Meta_Field where table_id = 5 AND Display_Row < 500 group by Subset_Value
I've been through my textbook, online articles, youtube ... you name it! Every reference to a correlated subquery that I have found involves only one table.
Two quick questions:
1. Is the below considered a correlated subquery? 2. Can you use a JOIN in an embedded SELECT statement? I ask because I have errors near the WHERE keyword in both subqueries.
USE MyGuitarShop SELECT EmailAddress, (SELECT MIN(OrderDate) FROM Orders JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID) AS OldestOrder, (SELECT Orders.OrderID FROM Orders JOIN OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS OrderID FROM Customers GROUP BY Customers.EmailAddress
Hi,I have a history table with about 400 million rows, with a uniquecomposite nonclustered index on two columns (object id and time period)which is used for most of the queries into the table, and an identitycolumn for the clustered primary key.Many of my queries use correlated subqueries to pull unique historyrows from the history table for each of a set of objects from theobject table, for instance, pulling the earliest history row for eachobject in a set. These correlated subqueries reference the object tableand return the primary key of the history table, e.g.:select *from lp_object linner join lp_object_history hon h.lp_object_id = l.lp_idwhere l.lp_set_id = 'SOME_LITERAL'and h.lp_id = (select top 1 lp_idfrom lp_object_historywhere lp_object_id = l.lp_idand lp_some_column > 0order by lp_time_period)Now, if lp_some_column is not indexed, this query has no choice but toread the entirety of every single history row for every object in theset where lp_set_id = 'SOME_LITERAL', so that it can determine iflp_some_column > 0, and because the history table is clustered by theidentity column rather than the ID of the relevant object whose historywe're tracking, the reads take forever - they have to bop all aroundthe disk. The sets I deal with tend to have about 5K objects in themand about 200K associated history rows.I'm considering reclustering by the (object id, time period) index, butthen my queries will need an extra bookmark lookup step to get the rowdata from the identity value returned by the correlated subquery. Ithink it will still be faster, though, so I will probably build a copyof the table with the alternative clustering scheme to run someperformance tests.What I'm wondering is, if I were to dispense with the identity columnaltogether and replace it with a composite primary key of (object id,time period), would I be still be able to use my correlated subqueries?Because then there wouldn't be a single column that uniquely identifieseach row in the history table and I don't think SQL Server supportsmulticolumn correlated subqueries.Thanks for reading,Seth
Hello All,I have a SQL Query with multiple correlated Subqueries in it. When itgets executed it runs rather slow due to the size of the QT table.Does anybody have any suggestions how to alter this query to make itrun faster, or any index suggestions to assist it with.Query is as follows:SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (JU_PROC_GRP < 2)AND (QT_QUOTE_JOB = 0))AS [PREPCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 1)) AS [MATCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 3)) AS [OUTCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND((JU_CATEG = 0) OR (JU_CATEG = 2) OR (JU_CATEG = 4))) AS [LABCOST]FROM SHWHERE SH_ADD_DATE = '5/FEB/2004'thanks a lot for any helpJason
Suppose I have this query, which shows each order and the price of the most expensive item in each order:
SELECT Ord.OrderID, Ord.OrderDate, MAX(OrdDet.UnitPrice) AS maxUnitPrice FROM Northwind.dbo.[Order Details] AS OrdDet INNER JOIN Northwind.dbo.Orders AS Ord ON Ord.OrderID = OrdDet.OrderID GROUP BY Ord.OrderID, Ord.OrderDate
I need to also show the ProductID that has MaxUnitPrice from the Order Details. I can't just add ProductID to the select list because I'd have to group by it, and then I'd get a row for each product, instead of a row for each order... I think I need a correlated subquery but can't work out how to do it!
I am running 2 versions of a correlated subquery. The two versiondiffer slightly in design but differ tremendously in performance....ifanyone can answer this, you would be awesome.The "bad" query attempts to build a result set using a correlatedsubquery. The part causing the error is that the correlated subqueryis part of a derived table (joining 3 tables). Trying to run the querytakes a long time and the more records in the source table, theperformance is exponentially worse. When i change the derived table toa fixed table, the query runs fast.I look at the Execution Plan in Query Analyzer and the majority of timeis taken by the Clustered Index Seek and by the Nested Loops/InnerJoin.************************************************** ************************************************** ******here is the "bad" query:************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 mi.id FROM messages miINNER JOIN identities i ON i.id=mi.identityidINNER JOIN licenses l on i.licenseid=l.idWHERE l.licenseKey = t1.licenseKey AND category = 'usage'ORDER BY mi.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******here is the "good" query************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 t2.id FROM temptable T2WHERE t2.licenseKey = t1.licenseKeyORDER BY t2.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******Thank you in advance
record_id Status Due_date549In Progress2004-06-02 00:00:00.000549Not Started2004-06-07 00:00:00.000549Not Started2004-06-08 00:00:00.000549Waiting 2004-05-31 00:00:00.000549Waiting 2004-06-04 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-06 00:00:00.000551Completed2004-05-07 00:00:00.000551Completed2004-05-10 00:00:00.000551Not Started1900-01-01 00:00:00.000552Not Started1900-01-01 00:00:00.000Hi I have this table with 3 columns.. What I want isDistinct(record_id),max(due_date) and Status.. I tried thisselect distinct(record_id),status,(due_date) from table1 where(due_date) in(select max(due_date) from table1 as A where a.record_id=record_idand a.due_date is not null group by a.record_id,status)So the result that I want isRecord Status Max(due_date)549Not Started2004-06-09 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-10 00:00:00.000Any help is appreciated..ThanksAJ
Note the following sql query. It contains two separate queries, an correlated subquery and outer query to work against the results of the subquery. Its purpose is twofold (1) get the TOP n ranked field entities using a certain value, (2) return all records for those entities.
SELECT MasterLoanID, NoteNumber, LendingOfficer,OriginalAmount, ReviewSampling FROM MasterLoanData WHERE Import_AsOfDate = '2006-05-31' AND BankID = '1' AND clientID = 1 AND LendingOfficer IN(SELECT TOP 3 LendingOfficer FROM MasterLoanData WHERE Import_AsofDate = '2006-05-31' AND ClientID = 1 AND BankID = '1' GROUP BY LendingOfficer ORDER BY SUM(OriginalAmount) DESC) ORDER BY LendingOfficer, Notenumber
Note that both queries need to filter the same fields -- import_AsofDate, BankID, and ClientID -- in order produce accurate results. Separate indexes exist for all three fields. Both queries work against the Sql Express database, however, when I combine them in a sql statement, Sql Express seems totally lost -- the query runs but never finishes -- I have to abort execution!
I've isolated the problem down to referencing of the import_AsofDate field in the outer query WHERE clause. If I remove that field reference from the outer WHERE clause, the query works quickly -- in seconds, however the results aren't accurate because I'm not getting a filter against the correct Import_AsofDate value. Note too that the same Access database executes the same query in seconds! Sql Express just seems totally confused by date reference contained in both WHERE clauses. I'd say that signifies a definite problem in Sql Express.
Has anyone experienced a similar problem with a correlated subquery and the same date field being referenced in both WHERE clauses of each query? I don't currently have a full fledged Sql Server database to test this query against, but seems as though it should work.
Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?
Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)
select OrderDate, ShipCountry, ShipCity, Freight, /* how do I get to the outer query's date range ? */ (SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight from Northwind.dbo.Orders O where ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL )
-- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
Original code (works in Sql Server 2000 but has error in Sql Server 2005) -- AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
Error message in Sql Server 2005 -- Server: Msg 8180, Level 16, State 1, Line 38 Statement(s) could not be prepared. Server: Msg 107, Level 16, State 1, Line 38 The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Corrected code --
AND SUBSTRING(CONVERT(CHAR,A_ED.EFFDT,121), 1, 10) <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone) FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
I've set up a correlated SQL query but i'm not entirely sure i've got it right. The purpose of it is to get all the unapproved hours for a TimeCode where the CodeOwner matches @UserName. It gets the unapproved hours for every code rather than the ones for the UserName. SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved,
(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName) as CodeOwner
FROM lward.tblHours
WHERE Approved is NULL The Username in tblHours is used for storing who is logging some hours. CodeOwner is the Owner of a paticular time code.tblHoursID, TimeCode, Date, Hours, Username, Comments, ApprovedtblCodesCodeID, CodeName, CodeDescription, CodeOwner, CodeCategoryThe only relationship is CodeID and TimeCode, CodeID is the primary key and TimeCode is the foreign.
I would like to combine the following 3 select statements:
1. select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2. Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3. select CASE COUNT(*) WHEN 0 THEN 'Compliant' ELSE 'Not Compliant' END from F_VIOLATIONS where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201. (a regulation has many sections)
678 10/17/06 OTHER STATUS 678 10/17/06 APPROVE> 678 10/24/06 APPROVE
789 10/04/06 DECLINE 789 10/06/06 OTHER STATUS 789 10/06/06 APPROVE>
I am looking for a sql which extract EARLIEST DATE IF STATUS = APPROVE OR DECLINE, ONLY NEED TO CONSIDER THESE TWO STATUSES.
for example serial = 789, I want to select the last row, since it is approved status.
The arrow in the right side , means i want to select that row from my sql. If both statuses(decline and approve) is falls for a serial number, then I want to select the row with approved status with earliest date. example sir = 789
I'm using Transact SQL for SQL 2000 and I'm having difficulties with a correlated sub-query.
I have a table called 'Results' like this...
Company_ID Product Spend Flag_Top25 1 Product A $100 1 Product B $250 1 Product C $450
I want to create a flag to identify if a companies spending for each product is within the Top 25% of spending within the product category across all products.
For example, the code below would identify the Top 25% of Companies with spending on Product A.
SELECT TOP 25 PERCENT Company_ID FROM Results WHERE Product = 'Product A' ORDER BY Spend DESC
I'm trying the following correlated sub-query to get this done and it is not working.
UPDATE Results A SET A.Flag_Top25 = 'Top 25% Customer' WHERE A.Company_ID in ( SELECT TOP 25 Percent B.Company_ID FROM Results B WHERE B.Spend > 0 and B.Product = A.Product ORDER BY B.Spend DESC )
I have a table similar to the following: ID ¦ Name ID ¦ Period From ¦ Period To ¦ Percentage ¦ --------------------------------------------------------------------------- Important - Each person can have more than one entry. What I am trying to do is get the last percentage that each person obtained. The only way I have been able to do this is by the following: SELECT * FROM myTable LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1 ON myTable.NameID = t1.NameID WHERE myTable.PeriodTo = t1.PeriodTo
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip
Hi, I am used to writing Sub-Correlated queries within my main queries. Although they work fine but i have read alot that they have performance hits. Also, as with time our data has increased, a simple SELECT statement with a few Sub-Queries tends to run slower which may be between 10-15 seconds. Following will be a simple example of what i mostly do: SELECT DISTINCT C.CusID, C.Name, C.Age, ( SELECT SUM (Price) FROM CusotmerOrder WHERE CusID_fk = CO.CusID_fk ) Total_Order_Price, ( SELECT SUM (Concession) FROM CusotmerOrder WHERE CusID_fk = CO.CusID_fk ) Total_Order_Concession, ( SELECT SUM (Price) - SUM (Concession) FROM CusotmerOrder WHERE CusID_fk = CO.CusID_fk ) Total_Difference FROM Customer C INNER JOIN CustomerOrder CO ON C.CusID = CO.CusID_fk ...... WHERE (conditions...) My question is what would be a better way to handle the above query? How can i write a better yet simple query with optimized performance. I would also mention that in some of my asp.net applications, i use inline queries assigned to SqlCommand Object. The reason i mention it that since these queries are written in some class files, how would we still accomplish what i have mentioned above. Kindly could any Query Guru guide me writing better queries. I shall be obliged...
I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.
The desired output is:
Yellow Blue 11/23/201311/19/2013 11/19/210310/01/2012 10/01/210210/08/2010 10/08/201012/14/2007
The actual output is:
Yellow Blue 11/23/201311/19/2013 11/19/210311/19/2013 10/01/210211/19/2013 10/08/201011/19/2013 11/23/201310/01/2102 11/19/210310/01/2102 10/01/210210/01/2102 10/08/201010/01/2102
The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.
Here is the SQL:
SELECT Long_List.Yellow,Short_List.Blue FROM ( SELECT DISTINCT BirthDate AS Blue FROM ( SELECT DISTINCT BirthDate FROM citizens
I have a 7 million line table named SecurityID with the following data: Date, Security, Identifier1, Identifier2, Identifier3
I am trying to reduce it to a table newSecurityID in the following form: FromDate, ToDate, SecurityId, Identifier1, Identifier2, Identifier3
This new table will have the first instance for each securityId with the identifying information. New rows will be added If any of the 3 identifying information changes. This isn't as simple as querying for the maximum and minimum value given each distinct group of identifiers because identifiers can change from an initial set and then change back to the initial values.
My plan was to first select all distinct (Security, Identifier1, Identifier2, Identifier3) into a temporary table. Then query the table SecurityID for the minimum date available which matches these 4 fields and find the corresponding maximum value. This doesn't seem to working as I had planned as I am getting one row for each date rather than when identifiers change. Plus its taking a really long time to finish.
Any help will be appreciated!
Here is my code:
select distinct SecurityId, Identifier1, Identifier2, Identifier3 into #DistinctSecurityID from SecurityID
I'm having some performance issues with a TSQL query. It's a complex statement but the main issue is the correlated query.
How can I convert this
SELECT TOP 5 (SELECT SUM(lt2.col3) FROM dbo.MyTable2 lt2 WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) AS Result FROM dbo.MyTable1 t1 ... to an inner join or a sql2012 window function?
By the way, I just added the TOP 5 myself while testing. It's not in the main query.
