Max for a group in a query

Database
Junior Supporter

Max for a group in a query

Hi,

my raw data is:

tablenamedbnamesize BYTES
BANK_DPBANK_RPT336,211,701,760.00
BANK_ACCTBANK_RPT256,012,658,688.00
bank_depositBANK_RPT212,332,027,392.00
bank_loan_BANK_RPT195,212,307,968.00
ACCT_POSTNMY_DATA1,297,444,027,392.00
EXPEN_MTHLMY_DATA1,293,033,611,264.00
GSDW_ISSMY_DATA1,208,248,501,248.00
BULK_EMAILMY_DATA642,915,017,728.00

 

My desired output is:

tablenamedbnamesize BYTES
BANK_DPBANK_RPT336,211,701,760.00
ACCT_POSTNMY_DATA1,297,444,027,392.00

Basically, i have databases and tables listed with their sizes and i want the tablename in a db with max size for every database and the raw data is not in a table, its output of a query that i wrote.This is just a sample data, i have more than 20 DB in the list. Although i am able to pull the data by creating temp table of my raw data and joining to itself, but i want to know an elegant query/single query to do it (may be usee max over() etc..)

Any ideas ?

--Samir Singh


Accepted Solutions
Junior Contributor

Re: Max for a group in a query

You can use MAX or ROW_NUMBER:

select * from tab
qualify
    row_number()  -- or RANK
    over (partition by dbname
          order by size desc) = 1

-- or
qualify
    max(size)
    over (partition by dbname) = size
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Max for a group in a query

You can use MAX or ROW_NUMBER:

select * from tab
qualify
    row_number()  -- or RANK
    over (partition by dbname
          order by size desc) = 1

-- or
qualify
    max(size)
    over (partition by dbname) = size
Junior Supporter

Re: Max for a group in a query

Thaks Diether !!