Get Parent with multiple children in desc order

by

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE myproblem (
   id int,
   parent int);

INSERT INTO myproblem values (5098,0),(5099,5098),(5100,5099),(5101,5099);
INSERT INTO myproblem values (5102,0),(5107,5102);
INSERT INTO myproblem values (5106,0),(5108,5106),(5110,5106);
INSERT INTO myproblem values (5109,0),(5111,5109);
INSERT INTO myproblem values (5112,0);

Query 1:

SELECT ParentUserType.Id As Parent ,count(ChildUserType.Id) AS 'Number of children'
    FROM myproblem AS ChildUserType
    LEFT JOIN myproblem AS ParentUserType ON ChildUserType.Parent = ParentUserType.Id
    GROUP BY ParentUserType.Id
    ORDER BY COUNT(ChildUserType.Id)

Results:

| Parent | Number of children |
|--------|--------------------|
|   5098 |                  1 |
|   5102 |                  1 |
|   5109 |                  1 |
|   5106 |                  2 |
|   5099 |                  2 |
| (null) |                  5 |

More Related Contents:

  1. How can I list all foreign keys referencing a given table in SQL Server?
  2. SQL query to select dates between two dates
  3. What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?
  4. Split function equivalent in T-SQL?
  5. SQL query to split column data into rows
  6. Calculating number of full months between two dates in SQL
  7. How to drop SQL default constraint without knowing its name?
  8. Replacing NULL with 0 in a SQL server query
  9. Is it possible to delete from multiple tables in the same SQL statement?
  10. Months between two dates
  11. Dynamic update statement with variable column names
  12. How to return the output of stored procedure into a variable in sql server
  13. A way to extract from a DateTime value data without seconds
  14. How to select only the first rows for each unique value of a column?
  15. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  16. Difference of two date time in sql server
  17. Modify Default value in SQL Server
  18. Unordered results in SQL
  19. Using PIVOT in SQL Server 2008
  20. Produce DISTINCT values in STRING_AGG
  21. sql group by only rows which are in sequence
  22. Function in SQL Server 2008 similar to GREATEST in mysql?
  23. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  24. Query times out from web app but runs fine from management studio
  25. Backup a single table with its data from a database in sql server 2008
  26. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  27. COALESCE Function in TSQL
  28. What is the meaning of SELECT … FOR XML PATH(‘ ‘),1,1)?
  29. SQL Sort Order with Null values last
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment