Using MAX() in a SQL Subquery

database diagram showing table relationships

One pro bono project I’m working on is improving a school website where parents can sign up for classes, view students’ grades, etc. One of the problems with the website was that the list of user accounts, which includes all parents and teachers, includes parents from previous years who no longer have students there.

The query for pulling this information was very simple:

SELECT * FROM UserAccounts ORDER BY lastName ASC

This query pulled all users and ordered them only by last name. The data was being dumped into an ASP.NET GridView with column sorting, and there were only a couple hundred people there, so it wasn’t completely unmanageable. However, dealing with all the parents who no longer have kids there did make visual searches more difficult. The request was to bring all current parents to the top of the list so that other admins don’t have to search through pages of people who no longer attend.

A quick fix for this would be to sort the list based on last academic year attended. We’ll do this by employing the MAX() function.

In addition to the UserAccounts table, there are other tables called “students” (which includes all current and past students) and “schedule” (which holds student schedules). In the schedule table, a column called “s_year” that holds the academic year (in the format of “2017-18”). We can join these tables based on user account IDs in the UserAccount table, the parentID column in the students table, and the student id (s_id) column in the schedule table.

One more thing: Since this list also includes people who have never had students there, the revised query will have to take that into account.

database diagram showing table relationships

The below query will not only solve this problem, but will also sort by first and last name. Users with no academic year (a NULL value) will be at the bottom of the list.

SELECT DISTINCT ua.*, sc.s_year FROM UserAccounts ua 
LEFT OUTER JOIN students s ON s.parentID = ua.ID 
LEFT OUTER JOIN schedule sc ON sc.s_id = s.ID 
WHERE sc.s_year IS NULL 
OR sc.s_year = (
   SELECT MAX(sc2.s_year) FROM schedule sc2 WHERE sc2.s_id = s.ID
) 
ORDER BY sc.s_year DESC, ua.lastName, ua.firstName

The only remaining thing to do is to add the year column to the GridView and make sure that sorting is enabled.

SQL Script to Find Text in all Database Tables and Views…Revised! Again!

The SQL script below, which modifies one I wrote last year, has been modified to work with SQL Server 2008 and newer versions. On these versions, when querying SQL Server to output the schema of a database table, length values of MAX are returned as -1. Since this script builds table variables that mimic the schema of the actual table, this needed to be accounted for so no errors would occur as a result of the MAX length being used.

If MAX values are not used, the output of this script should be no different than on the previous version.

-- Set database to search
USE dbname
GO
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here and set @SearchViews to 1 if views are to be included - 0 if not; no other code below changes
-- Set @PrintAllSQLStmts to 1 if you want all of the statements printed as messages
DECLARE @TestValue VARCHAR(8000) 
DECLARE @SearchViews BIT 
DECLARE @PrintAllSQLStmts BIT

SET @PrintAllSQLStmts = 0
SET @SearchViews = 0
SET @TestValue = 'text to search'
------------------------------------------------------------------------------------------------------------------------------------

/* If the version of SQL Server is 2005 or newer, these can be set to MAX rather than 8000 */
DECLARE @SQL VARCHAR(8000)
DECLARE @varSQL VARCHAR(8000)
DECLARE @WhereClause VARCHAR(8000)
/******************************************************************************************/

DECLARE @TableName VARCHAR(128)
DECLARE @varTableName VARCHAR(128)
DECLARE @TableSchema VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
DECLARE @MaxLength VARCHAR(4)
DECLARE @NMaxLength VARCHAR(4)

IF @@VERSION LIKE 'Microsoft SQL Server  2000%'
	BEGIN
		SET @MaxLength = '8000'		
		SET @NMaxLength = '4000'		
	END
ELSE
	BEGIN
		SET @MaxLength = 'MAX'
		SET @NMaxLength = 'MAX'
	END
	
DECLARE @SchemaObjects TABLE (
	TABLE_NAME VARCHAR(128), 
	TABLE_SCHEMA VARCHAR(128)
)

DECLARE @TypeTable TABLE (
	UserType VARCHAR(128) PRIMARY KEY,
	BaseType VARCHAR(128)
)

DECLARE @SizableTypes TABLE (
	TypeName VARCHAR(128) PRIMARY KEY
)

INSERT INTO @SizableTypes VALUES ('VARBINARY')
INSERT INTO @SizableTypes VALUES ('VARCHAR')
INSERT INTO @SizableTypes VALUES ('NVARCHAR')
INSERT INTO @SizableTypes VALUES ('CHAR')
INSERT INTO @SizableTypes VALUES ('NCHAR')

INSERT INTO @TypeTable
SELECT ut.name, bt.name FROM SysTypes ut, SysTypes bt
 WHERE ut.xusertype > 256 AND bt.xusertype < 256
 AND ut.xtype = bt.xtype

INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'

IF @SearchViews = 1
BEGIN
	INSERT INTO @SchemaObjects
	SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
END

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME, TABLE_SCHEMA FROM @SchemaObjects

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @WhereClause = ''

	DECLARE ColumnCursor CURSOR FAST_FORWARD FOR
	SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @TableSchema AND ((DATA_TYPE IN ('CHAR','NCHAR','VARCHAR','NVARCHAR')) AND (CHARACTER_MAXIMUM_LENGTH IS NOT NULL) AND (CHARACTER_MAXIMUM_LENGTH >= LEN(@TestValue)) OR DATA_TYPE IN ('TEXT','NTEXT'))
	OPEN ColumnCursor
	FETCH NEXT FROM ColumnCursor INTO @ColumnName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF LEN(@WhereClause) > 0 SET @WhereClause = @WhereClause + ' OR '
		SET @WhereClause = @WhereClause + '(CONVERT(VARCHAR,[' + @ColumnName + ']) LIKE ''%' + @TestValue + '%'')'
		FETCH NEXT FROM ColumnCursor INTO @ColumnName
	END
	CLOSE ColumnCursor
	DEALLOCATE ColumnCursor	
	IF LEN(@WhereClause) > 0
	BEGIN		
		SET @varTableName = REPLACE(@TableName,' ','_') 
		SET @varSQL = 'DECLARE @' + @varTableName + ' TABLE (' + CHAR(10) 		
		SELECT @varSQL = @varSQL + ' [' + sc.name + '] ' + 		
		CASE WHEN st.name = 'TIMESTAMP' THEN 'DATETIME' 			 
			 WHEN tt.UserType IS NOT NULL THEN UPPER(tt.BaseType) 			 
		ELSE UPPER(st.name) END +
		CASE WHEN tt.BaseType IN (SELECT TypeName FROM @SizableTypes) OR st.name IN (SELECT TypeName FROM @SizableTypes) THEN '(' + 
			CASE WHEN (LEFT(tt.BaseType,3) = 'VAR' OR LEFT(st.name,3) = 'VAR') AND (sc.[length] >= 8000 OR sc.[length] = -1) THEN @MaxLength
				 WHEN (LEFT(tt.BaseType,3) = 'NVA' OR LEFT(st.name,3) = 'NVA') AND (sc.[length] >= 4000 OR sc.[length] = -1) THEN @NMaxLength			 				 
			ELSE CAST(sc.[length] AS VARCHAR) END + ') ' 
		ELSE ' ' END +
		CASE WHEN sc.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ',' + CHAR(10)
		FROM SysObjects so
		JOIN SysColumns sc ON sc.id = so.id
		JOIN SysTypes st ON st.xusertype = sc.xusertype
		LEFT OUTER JOIN @TypeTable tt ON st.name = tt.UserType
		WHERE so.name = @TableName
		ORDER BY sc.colid		
		
		SET @SQL =  CHAR(10) + 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM [' + @TableSchema + '].[' + @TableName + '] WHERE ' + @WhereClause + ';' 
		SELECT @varSQL = SUBSTRING(@varSQL,1,LEN(@varSQL) - 2) + CHAR(10) + ');' + CHAR(10)
		
		SET @varSQL = @varSQL + 'INSERT INTO @' + @varTableName + CHAR(10) + 'SELECT * FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(10) + 'WHERE ' + @WhereClause + ';' + CHAR(10)
		SET @varSQL = @varSQL + 'IF EXISTS(SELECT * FROM @' + @varTableName + ')' + CHAR(10) 
		SET @varSQL = @varSQL + 'BEGIN' + CHAR(10)
		SET @varSQL = @varSQL + '   SET NOCOUNT OFF;' + CHAR(10)		
		
		IF @PrintAllSQLStmts = 0
			SET @varSQL = @varSQL + '   PRINT ''' + CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + CHAR(10) + REPLACE(@SQL,'''','''''') + ''';'  + CHAR(10)
		ELSE
			PRINT CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + @SQL + CHAR(10)		

		SET @varSQL = @varSQL + '   SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM @' + @varTableName + ';' + CHAR(10)		
		
		SET @varSQL = @varSQL + '   SET NOCOUNT ON;' + CHAR(10)
		SET @varSQL = @varSQL + 'END' + CHAR(10)
		SET @varSQL = @varSQL + CHAR(10) + CHAR(10) + CHAR(10)
		--PRINT @varSQL
		EXEC (@varSQL)
		IF @@ERROR <> 0
		BEGIN 
			PRINT '/* Statement causing error */' + CHAR(10) + CHAR(10) + @varSQL + CHAR(10) + '/* End Statement */' + CHAR(10) 
			PRINT 'Length of SQL statement: ' + CONVERT(VARCHAR,LEN(@varSQL)) + CHAR(10)
		END
	END
	FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor
%d bloggers like this: