INSERT INTO table2(frmdt,todt)

SELECT ca.frmdt,ca.todt

FROM table1 t

CROSS APPLY(SELECT FDT1, TODT1

UNION ALL

...

WITH CTE AS (

SELECT Pk_Id,Lnno,Amount,Flag,

ROW_NUMBER() OVER(PARTITION BY Lnno ORDER BY Amount DESC) AS rn

FROM #Temp)

UPDATE CTE

SET Flag='Y'

WHERE rn=1;

Strange, but there seems to be a number of old threads being answered

http://www.sqlservercentral.com/Forums/Topic950491-145-1.aspx

http://www.sqlservercentral.com/Forums/Topic334022-149-1.aspx

http://www.sqlservercentral.com/Forums/Topic512190-338-1.aspx

WITH CTE AS (

SELECT ID ,Name ,SAl,Rownum,

ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Rownum DESC) AS rn

FROM #Abc)

SELECT ID ,Name ,SAl,Rownum

FROM CTE

WHERE rn=1

ORDER BY...

... uses your method here[/url]

SELECT DocumentNo_,

MAX(CASE WHEN DimensionCode='DEPARTMENT' THEN DimensionValueCode END) AS DimensionValueCode_1,

MAX(CASE WHEN DimensionCode='TRADETYPE' THEN DimensionValueCode END) AS DimensionValueCode_2

FROM...

dwain.c (7/4/2013)

---

I'm always interested in cases like this so with more time on my hands than I care to admit, I came up with the following test harness:

CREATE TABLE #TABLE1...

You haven't provided enough information to answer this fully, but see if this helps

DECLARE @t TABLE(ID INT, Item VARCHAR(10), ShipTo VARCHAR(10),FCST01 VARCHAR(10),FCST02 VARCHAR(10),FCST03 VARCHAR(10));

INSERT INTO @t(ID, Item, ShipTo, FCST01, FCST02,...

Try this

WITH CTE1 AS (

SELECT Filename,SuccessFailIndicator,checkdate,

ROW_NUMBER() OVER(PARTITION BY Filename ORDER BY checkdate DESC) AS rn

FROM TABLE2)

SELECT t1.jobname

FROM TABLE1 t1

LEFT OUTER JOIN CTE1 c...

If you're using SQL Server 2012 you can use OFFSET..FETCH NEXT

DECLARE @PageNumber INT;

SELECT *

FROM myTable

OFFSET (@PageNumber-1) * 10000 ROWS

FETCH NEXT 10000 ROWS ONLY;

One possible solution, probably lots of others...

WITH Starts AS (

SELECT a.PatientID,a.AdmissionDate,

ROW_NUMBER() OVER(PARTITION BY a.PatientID ORDER BY a.AdmissionDate) AS rn

FROM TC79 a

WHERE NOT EXISTS(SELECT...

WITH CTE AS (

SELECT CustNo, ActivityStartDate, ActivityEndDate, DiffHours, ActivityEnd,

ROW_NUMBER() OVER(PARTITION BY CustNo ORDER BY ActivityStartDate) AS rn1,

...

SELECT @XML.value('(/CP/CustomProperty1/text())[1]','VARCHAR(20)')

Lowell (6/26/2013)

---

do you mean how do you get teh newly created PK values for data you inserted? especially so you can then do a subsequent insert into a child table?

the...

As John suggested, note you'll need to use DATETIME rather than SMALLDATETIME

WITH CTE AS (

SELECT callref

, repid

, updatetime = CAST(updatetime AS DATETIME)

, rn =...