SavantTheIncredible SavantTheIncredible - 6 months ago 14
SQL Question

group by TABLE_SCHEMA, order by TABLE_NAME

I try to have all the Table_Schema names in order and then order by for each table_schema the table_Name. I try it like this:

SELECT TABLE_SCHEMA , TABLE_NAME, (TABLE_TYPE)
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_type = 'BASE TABLE'
GROUP BY TABLE_SCHEMA
ORDER BY TABLE_NAME DESC


Thank you

Answer

GROUP BY actually "rolls up" all similar query results into one row. https://msdn.microsoft.com/en-us/library/ms177673.aspx

You just want an order by with two columns in it. Try this:

SELECT TABLE_SCHEMA , TABLE_NAME, (TABLE_TYPE)  
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_type = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME
Comments