TSQL provides a TOP command to retrieve a portion of the result set, but no MIDDLE command.  You can use partitioning (NTILE) to mimic the MIDDLE command.

I created a simple four record data set (scores) for this example:

bob, 12
mary, 15
jim, 19
lenny, 29

And here’s the code that divides the result set (ordered by score) into four partitions and uses WHERE to limit the set to partition 2 and 3 (the middle 50%).

;WITH ScoreRanking AS
(
SELECT *, NTILE(4) OVER (ORDER BY Score) [Position]
FROM Scores
)

SELECT PlayerID, Score
FROM ScoreRanking
WHERE Position IN 2, 3

We ran across the need to create some statistics on a set of results created by a query (with a group by statement). This kind of scenario is real simple by using a temporary table:

-- Create your temporary table
WITH tempTableName (theID, numberOfInstances) AS
(
    SELECT theID, COUNT(*) AS numberOfInstances
    FROM tableName
    GROUP BY theID
)

-- Generate your statistics
SELECT AVG(numberOfInstances) AS [Average Number of Instances]
FROM tempTableName
WHERE numberOfInstances > 0 ;
GO

Here’s a quick way to find out what port(s) a certain process/program is using. First, get the PID with tasklist. I’ll use the communicator.exe process in this example.

Next, use the PID to get the port number(s) with netstat.

Sadly, I spent 30 minutes looking for this yesterday.

It is plenty easy to add a table to a schema:

use databaseName

go

alter schema schemaName transfer dbo.tableName

go

Good Times!

If you would like to copy a table (or just a few columns) for backup / testing purposes:

SELECT *
INTO copy_of_original_table
FROM original_table

You do not have to create the new table ahead of time, but you will need to verify identity columns and create indexes if you screw up the original table.

Filed under: MSSQL
Tagged under: MSSQL Transact TSQL "MS SQL"
1 Comment