We are running sql server 2005 standard edition. In the query below, it has top percent clause in the subquery and returns various rows, 2, or 3, or 4 rows each times when running manually one after another. Does anyone have an explaination?
SELECT
dbo.WOB_STEP.STEP_UID
FROM
dbo.WOB_STEP
INNER JOIN dbo.CURRENT_DATES CD1 ON (dbo.WOB_STEP.CD_UID=CD1.CD_UID)
WHERE
( dbo.WOB_STEP.STEP_UID IN (SELECT TOP (50) PERCENT WITH TIES STEP_UID FROM dbo.vWOB_STEP_RANDOM WHERE OPERATOR_NAME = 'Jennelyn Llobrera' AND VENDOR_RETURN_FLAG = 1
ORDER BY RandomID) )
I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?
I am trying to get the percent correct for each ID and each testID from the table above. Also, IsCorrect column valuse (1= correct; 0= not correct)
select ID, count(Iscorrect) [total], (select count(Iscorrect) from dbo.StudentTestAnswers
where TestID=1 and Iscorrect=1) as totalCorrect,((select count(Iscorrect) from dbo.StudentTestAnswers
where TestID=1 and Iscorrect=1)/count(Iscorrect)*100) as PctCorrect
from StudentTestAnswers
where testID=1
Group by ID, TestID
The output should look like this after running the T-SQL code above. But I am getting some weird number on totalCorrect and PctCorrect columns. Not sure if my SQL code is even correct.
Hi,I'm trying to outer join to a maximum date value using a subquery inorder to return company information and the last activity dateassociated. The basic working "sub" query is:SELECT actcomp.company_id, MAX(act.due_date)FROM oncd_activity_company AS actcomp, oncd_activity AS actWHERE actcomp.activity_id = act.activity_idGROUP BY company_idThe overall (abbreviated) query I'm trying to insert this select intois:SELECT oncd_company.company_id,oncd_company.company_name,act.due_dateFROM oncd_companyLEFT OUTER JOIN oncd_activity_company ON (oncd_company.company_id =oncd_activity_company.company_id)LEFT OUTER JOIN (SELECT actcomp.company_id, MAX(act.due_date)FROM oncd_activity_company AS actcomp, oncd_activity AS actWHERE actcomp.activity_id = act.activity_idGROUP BY company_id) ON(oncd_activity_company.company_id = actcomp.company_id)I'm receiving an "invalid syntax near keyword ON" error (highlightappears on the period in "oncd_activity_company.company_id").Any help would be appreciated!Thanks,Chris.
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
I am using sql server 2005. I stuck out in a strange problem. I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
In the Sql below, sample from William Pearson, the amount Spend is in descending order and the Rank number is in ascending order. Like this:
Spend Rank 24 1 12 2 10 3 9 4
What I wish to accomplish is:
Spend Rank (descending) 24 4 12 3 10 2 9 1
Please let me know what I need to accomplish it.
Thanks for the help
Victor
SELECT CompanyName, Spend, (SELECT COUNT(*) FROM ACC0704 AS CoSpendTotal WHERE ACC0704.Spend <= CoSpendTotal.Spend) AS Rank FROM ACC0704 ORDER BY Spend DESC
SOFWARES TABLE SOFTWARE_ID SOFTWARE_NAME -------------- ----------------- 1 X 2 Y 3 Z
I need a result like; COMPANY_ID COMAPNY_NAME SOFTWARES ------------- ---------------- ------------ 10 My Company X, Y, Z
I've used a query like;
SELECT COMPANY_ID ,COMPANY_NAME ,( SELECT SOFTWARE_NAME FROM COMPANY_SOFTWARES COS, SOFTWARES S WHERE S.SOFTWARE_ID=CS.SOFTWARE_ID AND COS.COMPANY_ID = C.COMPANY_ID ) AS SOFWARE_NAMES FROM COMPANY C WHERE AND C.COMPANY_ID = 10
But I get an error message; Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I've read that in MySQL there is CONCAT function for the result I want but I couldn't find any solution in MSSQL server, any solutions, suggestions?
i have two tables Student and Daily Attendance with following structure ,as i want to extract students of a class order by roll number which are not in Daily Attendance table , i know it will be solved by subquery but I don't know how to solve it.
This is probably very elementary to someone more experienced, but I'm having a hard time coming up with a way to do the following:
SELECT * FROM Transactions WHERE MyField LIKE (SELECT MyValues FROM SearchValues)
But I can't because the subquery will return multiple rows. That's all I'm really trying to do - search all the rows in Transactions.MyField for any of the search values returned from the subquery.
And I can't restructure the SearchValues table to combine them into a single-row comma-delimited field or anything like that.
Hi there, I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results. Here is my basic structure:
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
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
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')
HI All, I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that?
This is my sp, and when it returns the data i also want it to return the how many rows are the result.
Trying to do a paging scheme without using #Temp tables in MS SQL 7.0
Client calls a sp passing 1 and sql returns the first 100 records. Client sends a sp passing 100 and gets the next 100 records.
Process continues till @@fetch_status <> 0 or the client can stop sending requests.
I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.
I have a question, Select top 10 * from employee the above statement return top 10 row. but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005
hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this? SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND... OR SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @OrderAnd SELECT TOP(@pagesize) FROM (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @Order)ORDER BY @revOrder
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
The following query returns 2142 rows which is correct.
Code: select COUNT(*), sum(v.currentMarket) from TRMaster m inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A' where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1 group by m.Year, m.Parcel having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows. I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.
How can a SQL statement be written to return a specified range ofrows? For example:-- tblContact-- (-- SSN char(9),-- FirstName varchar(50),-- LastName varchar(50)-- )-- This table contains 500 rows.Select * from tblContact -- Return only rows 5 through 10Thanks
