-2

In Snowflake, I'm querying the Information_Schema for the number of columns in each of a series of listed tables. How do I write my query so that Snowflake will return the results in the same order that I've listed the table names in the query? I tried CASE WHEN 'table1' THEN 1, CASE WHEN 'table2' THEN 2,...etc, but had no luck. Alternatively, if SQL Server has a better solution for this, I could query it there.

6
  • What is stopping me from asking questions in as user friendly a way as I would like to ask them has been my inability to figure out this site's mark-up language. This is apparently more difficult than SQL. Every time I think I understand how to post code on this site, I get it wrong; it comes out as regular text; and then I get berated and voted down by the moderators. And just from leaving this message I'll probably be voted down. And yes, I've read the pages on how to post.
    – Jimbo
    Commented Aug 28, 2023 at 20:52
  • Markdown help is available at dba.stackexchange.com/editing-help
    – Paul White
    Commented Aug 29, 2023 at 12:34
  • Honestly, markdown doesn't appear to be the issue with your Post here. The clarity of what you're actually asking is the issue. Having a little more clearer information such as the table definition(s), some sample data, expected results, and the full query you wrote / are stuck on would've gone a long way here.
    – J.D.
    Commented Aug 29, 2023 at 20:33
  • 1
    @Jimbo That's fine, but a mechanic can't fix your car if you don't show him the car. All of the information I added can be anonymized if needed, before providing. It's just to get a better picture of what we're working with. Also just a heads up, if you want to notify someone of your comment you can use the @ before their name. I didn't realize you replied to me until I manually went back to this Post to check.
    – J.D.
    Commented Aug 30, 2023 at 13:55
  • 1
    @J.D. Ah, got it. Ok, thank you J.D.
    – Jimbo
    Commented Aug 31, 2023 at 14:22

2 Answers 2

0

I don't have a Snowflake instance at hand, but since you've tagged it with SQL server as well…

You can provide your filter in a table expression (or temp table or table variable) along with a column used for ordering (called Ordinal in my example).

DB fiddle demo

SELECT 
    d.name
    , d.database_id
    , v.Ordinal
FROM sys.databases AS d
JOIN (
    VALUES 
        (1, 'model')
        , (2, 'msdb')
        , (3, 'master')
        , (4, 'tempdb')
) v (Ordinal, dbName)
    ON d.name = v.dbName
ORDER BY v.Ordinal

enter image description here

-1

I can order the results of my query in the same order as my query with a series of case statements, putting END at the end of each case statement:

CASE WHEN table_name =  'my_table1' THEN 1 END, 
CASE WHEN table_name = 'my_table2' THEN 2 END,

etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.