Examples:
-- SELECT ... FROM SELECT -- 'AS Temp' is important here SELECT BookId, Title FROM (SELECT * FROM Books) AS Temp -- Conditional ORDER BY DECLARE @SortOrder INT ... SELECT * FROM Book ORDER BY CASE WHEN @SortOrder=0 THEN Title END ASC, CASE WHEN @SortOrder=1 THEN BookId END DESC -- Show all tables in the current database -- Columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME -- Show columns for a given table SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE LOWER(TABLE_NAME)='customer' ORDER BY ORDINAL_POSITION
Examples with sample data
#items is a test table:
-- Create the test table. CustomerId is a foreign key to a parent table Customer. CREATE TABLE #items ( ItemId INT IDENTITY(1,1), ItemType CHAR(1), CustomerId INT, Price MONEY, PurchaseDate DATETIME ) -- Insert some sample data. INSERT INTO #items VALUES ('A', 1, 5.99, '20120522') INSERT INTO #items VALUES ('A', 1, 60.25, '20121008') INSERT INTO #items VALUES ('B', 1, 12.0, '20120317') INSERT INTO #items VALUES ('B', 1, 3.99, '20120317') INSERT INTO #items VALUES ('C', 1, 23.80, '20120317') INSERT INTO #items VALUES ('A', 2, 20.60, '20120905') INSERT INTO #items VALUES ('C', 2, 9.99, '20121106') -- Example goes here: -- ... -- Drop the test table when we are done. DROP TABLE #items GO
Example #1: Select the following items:
SELECT ItemId, ItemType, Price FROM #items WHERE ItemType=(CASE WHEN Price>10 THEN 'A' ELSE 'B' END) -- Results: ItemId ItemType Price ----------- -------- --------------------- 2 A 60.25 4 B 3.99 6 A 20.60
Example #2: Select the last purchase date for each customer:
SELECT DISTINCT CustomerId, (SELECT TOP 1 PurchaseDate FROM #items rec2 WHERE rec1.CustomerId = rec2.CustomerId ORDER BY PurchaseDate DESC) AS LatestPurchase FROM #items rec1 -- Results: CustomerId LatestPurchase ----------- ----------------------- 1 2012-10-08 00:00:00.000 2 2012-11-06 00:00:00.000
Example #3: Select ItemType of an item with the highest price (for a given customer):
SELECT ItemType = (SELECT TOP 1 ItemType FROM #items WHERE CustomerId=1 ORDER BY Price DESC) -- Results: ItemType -------- A
Other examples:
-- Correlated queries -- Select the first orders placed by each customer: SELECT ord.CustomerID, ord.OrderID, ord.OrderDate FROM [ORDER] ord WHERE ord.OrderDate = (SELECT MIN(OrderDate) FROM [ORDER] WHERE CustomerID = ord.CustomerID) ORDER BY CustomerID
Obtain the count using dynamic SQL:
DECLARE @SQL NVARCHAR(2000), @cnt INT SET @SQL = 'SELECT @cnt = COUNT(*) FROM MyTable' EXEC sp_executesql @query = @SQL, @params = N'@cnt INT OUTPUT', @cnt = @cnt OUTPUT