Query Help! 1

[alwayshouston]

Hi All
I have a table/query (average of dept salary by year) with below data:

DEPT Y1 Y2 Y3 Y4 Y5
ACCT 5 6
ACCT 5 6 7 8 9
ACCT 5 6 7 8
ACCT
ACCT 5 6 7
FIN 9 8 7 6
FIN 9 8
FIN
FIN 9

I would like to get the results in which maximum of periods are filled. I would need to show below results:

DEPT Y1 Y2 Y3 Y4 Y5
ACCT 5 6 7 8 9
FIN 9 8 7 6

What is the easiest way to write a query to show maximum filled fields as mentioned above?

Thanks in Advance!

 

[gol4]

build the query similar to the query that creates the average but replace avg() with Max()

 

[alwayshouston]

Thanks Gol4!