Max for a group in a query

[teradatauser2]

Hi,

my raw data is:

tablename	dbname	size BYTES
BANK_DP	BANK_RPT	336,211,701,760.00
BANK_ACCT	BANK_RPT	256,012,658,688.00
bank_deposit	BANK_RPT	212,332,027,392.00
bank_loan_	BANK_RPT	195,212,307,968.00
ACCT_POSTN	MY_DATA	1,297,444,027,392.00
EXPEN_MTHL	MY_DATA	1,293,033,611,264.00
GSDW_ISS	MY_DATA	1,208,248,501,248.00
BULK_EMAIL	MY_DATA	642,915,017,728.00

My desired output is:

tablename	dbname	size BYTES
BANK_DP	BANK_RPT	336,211,701,760.00
ACCT_POSTN	MY_DATA	1,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

[dnoeth · Accepted solution]

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

[teradatauser2]

Thaks Diether !!