I want to use the ROW_NUMBER() to get… To get the max(ROW_NUMBER()) –> Or i guess this would also be the count of all rows I tried doing: SELECT max(ROW_NUMBER() OVER(ORDER BY UserId)) FROM Users but it didn’t seem to work… To get ROW_NUMBER() using a given piece of information, ie. if I have a … Read more
I have a case where using a JOIN or an IN will give me the correct results… Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using MSSQL) 6 Answers 6
USE AdventureWorks2008R2; GO SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Total’ ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Avg’ ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Count’ ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Min’ ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Max’ FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664); I read about that clause and I don’t understand why I need it. … Read more
I created a database on my local machine and then did a backup called tables.bak of table DataLabTables. I moved that backup to a remote machine without that table and tried to do a restore but get the following error: System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘c:\Program … Read more
A few examples to show, just incase: Inline Table Valued CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO Multi Statement Table Valued CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder … Read more
I need to implement the following query in SQL Server: select * from table1 WHERE (CM_PLAN_ID,Individual_ID) IN ( Select CM_PLAN_ID, Individual_ID From CRM_VCM_CURRENT_LEAD_STATUS Where Lead_Key = :_Lead_Key ) But the WHERE..IN clause allows only 1 column. How can I compare 2 or more columns with another inner SELECT? 13 Answers 13
I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes. This is the case when the query is executed from Query Analyzer or from my C# program via SqlCommand.ExecuteReader(). Calling (or not calling) … Read more
This question already has answers here: How to concatenate text from multiple rows into a single text string in SQL Server (47 answers) Closed 5 years ago. To illustrate, assume that I have two tables as follows: VehicleID Name 1 Chuck 2 Larry LocationID VehicleID City 1 1 New York 2 1 Seattle 3 1 … Read more
I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event. My solution to this is to add a CTE … Read more
I want to convert a string like this: ’10/15/2008 10:06:32 PM’ into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE(’10/15/2008 10:06:32 PM’,’MM/DD/YYYY HH:MI:SS AM’) This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. That seems … Read more
