SQL–how to find nth row / nth order element

Example below is self-explanatory – you should assign rank by desirable criteria and then just group by your categories (product id and name is sample below), selecting only entries of certain rank. In sample below you would select all entries with second biggest ID per category.

CREATE TABLE  #test ( id int, ProductName VARCHAR(25) )
insert into #test
select 1, 'Apple' union all
select 2, 'Apple' union all
select 5, 'Apple' union all
select 3, 'Orange' union all
select 4, 'Orange' union all
select 10, 'Orange'

SELECT * FROM #test

SELECT maxID FROM
(
    SELECT MAX(id) AS maxID, ProductName AS nn, RANK() OVER (PARTITION BY ProductName ORDER BY id DESC) AS MyRank
    FROM #test
    GROUP BY id, ProductName
) tmp
WHERE tmp.MyRank = 2

Solution for "Impossible to log SQL 2008 performance counters under Windows 2008 64 bit?"

Alex Pinsker: Impossible to log SQL 2008 performance counters under Windows 2008 64 bit?

Here is solution, found by our DBA: 1. Open regedit.exe 2. Find the key "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance" 3. Find the value named "Performance counters disabled" 4. Ensure, that its value is NOT "4" or "1". If it is - enforce it to "0" 5. Notice file name in value of "PerfIniFile" key and copy it to clipboard 6. Close the regedit 7. Open cmd and go to Binn folder of Sql Server installation. Usually in "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn" 8. Run the following commands: 8.1 unlodctr mssqlserver 8.2 lodctr [clipboard filename from (5.) above] 8.2.1 lodctr [clipboard filename from (.5) above] (this only needs for sure, that counters are installed - there will be a message notifying on that) 8.3 lodctr /T:mssqlserver 9. Restart the server 10. Use perfmon to ensure, that counters for Sql Server reappeared

What you see in Working Set of SQL Server 2008 process (sqlservr.exe) is NOT amount of memory allocated by the process

Here is some extended info.

Technorati Tags: SQL 2008,AWE,Task Manager,Working Set

SQL Tips: Using MERGE SQL statement in SQL 2008

... And still one more from the same generous source ... SQL Server 2008's new MERGE statement allows you to insert, update, or delete data based on certain join conditions in the same statement. MERGE SalesArchive AS SA USING (     SELECT         CustomerID,         LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),         TotalSalesAmount = SUM(SaleAmount),         TotalSalesCount = COUNT(*)     FROM SalesFeed     GROUP BY CustomerID ) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount) ON (SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate ) WHEN NOT MATCHED THEN INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate)     VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE()) WHEN MATCHED THEN UPDATE     SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount,             SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount,             SA.UpdatedDate = GETDATE(); Also, there is a new ability to pass a table variable to the stored procedures: DECLARE @MyTable TABLE (Col1 INT, Col2 Varchar(100)) EXEC MySP @Par1 = @MyTable OUTPUT