November 29, 2011 at 1:14 am
HI
i am having query like this in oracle when i try to execute this query in sql server i got error
so how to write this query in sql server
delete From EMP where [EMP_ID],[DEPT_ID) not in
(select [EMP_ID],MAX([DEPT_ID]) From EMP group by [EMP_ID])
November 29, 2011 at 1:33 am
Try it with CTE...
Syntax:
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
Example:
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
November 29, 2011 at 1:41 am
You are new to SQL Server so I am providing few more information on sub-query.
Subquery Rules
A subquery is subject to the following restrictions:
•The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
•If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
•The ntext, text, and image data types cannot be used in the select list of subqueries.
•Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
•The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
•The COMPUTE and INTO clauses cannot be specified.
•ORDER BY can only be specified when TOP is also specified.
•A view created by using a subquery cannot be updated.
•The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.
November 29, 2011 at 1:45 am
shall u plz tell how to write this query in sql server
delete From EMP where [EMP_ID],[DEPT_ID) not in
(select [EMP_ID],MAX([DEPT_ID]) From EMP group by [EMP_ID])
November 29, 2011 at 1:52 am
Hmmm... I wanted to help you in a way that might helpful for long run i.e. understanding CTE & sub-queries. It seems you want short term solution (spoon feed).
No Problem... I will take another route 😉 You are familiar with Oracle SQL. So can you re-write this query with JOINs? Same query should run in SQL Server as well.
November 29, 2011 at 2:16 am
delete from a
from EMP a
inner join (select [EMP_ID],MAX([DEPT_ID]) DEPT_ID From EMP group by [EMP_ID]) b
on a.EMP_ID = b.EMP_ID
and a.DEPT_ID < b.DEPT_ID
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply