The subquery is unnecessary. You can write this as follows:

SELECT iStudentid as StudentId, OldClassID, NewClassID

FROM @tblMoveStudentList as StudentMoveSummary

FOR XML auto,ROOT('ArrayOfStudentMoveSummary'),ELEMENTS

If you alias the table, it will use that alias...

ColdCoffee (11/3/2011)

---

Even better solution

DECLARE @MaxIDoFMaxPrice INT

; WITH RowWithMaxValue AS

(

SELECT TOP 1 ID

FROM @Table

ORDER BY PRICE DESC

)

SELECT @MaxIDoFMaxPrice = ID FROM RowWithMaxValue

SELECT * FROM @Table

ORDER BY CASE WHEN ID =...

You are potentially missing some records like '2011-11-30...

Not by itself, but the fact that it's...

Sean Lange (11/2/2011)

---

I agree with Lynn, we need something to go on here.

However, just in the skeleton you posted you can increase performance AND accuracy to at least some degree...

getusama-1105611 (11/2/2011)

---

I tired that but gave the below error:

Msg 157, Level 15, State 1, Line 100

An aggregate may not appear in the set list of an UPDATE statement.

This is the...

Since windowed functions (like Row_Number) can't be used directly in an UPDATE statement, you'll need to use a CTE. Otherwise, you have everything that you need to solve this...

ROW_NUMBER() partitioned by your group will give you the results that you want. If you want tested code, provide the sample data and results that Phil asked for.

Drew

Sean Lange (11/1/2011)

---

It just isn't a "here is the code for you" type of solution. You are going to have to read the second article I linked and understand how...

ramanamreddy (11/1/2011)

---

but in my requirement, my code works dynamically that means in my real work I don't know how many records are going to repeat with different phone no.s, I...

Even though it doesn't look like it, you...

You would just use your CASE expression for your PARTITION BY expression.

SELECT Rank() Over( PARTITION BY CASE WHEN ... THEN ... END ORDER BY expression )

FROM YourTable

Drew

Here is a shot in the dark at a solution.

;

WITH Totals_Ranked AS (

SELECT *, Row_Number() OVER (PARTITION BY m10_Class_Code ORDER BY Month_Start DESC) AS Month_Rank

, Row_Number() OVER ( PARTITION BY...

It wasn't chastisement, it's realism. You have to understand that everyone here is a volunteer. The more hurdles you place in front of people, the fewer will even...

First, the idea behind including a SAMPLE is that it is large enough to provide a realistic representation of the data, BUT NO LARGER. Since you are grouping on...