Re: Select Top 100 in a Group By

[Carel <coteijgeler@xxxxxxxxx>]

What about:

SELECT CUSTOMER, DUE_DATE, SUM(USAGE) AS total
FROM BILLHIST
GROUP BY CUSTOMER, DUE_DATE
ORDER BY total DESC, customer, due_date
FETCH FOR 100 ROWS ONLY

KInd regards,
Carel Teijgeler


Op 1-2-2019 om 22:53 schreef Rob Berendt:
> For some reason this makes me thing of the genre of functions defined by CUBE, ROLLUP, etc.
> But I've got a yen to get out of the office...
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mike Wills
> Sent: Friday, February 1, 2019 4:29 PM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Subject: Select Top 100 in a Group By
>
> Hey everyone, it's been a while.
>
> I am hoping for a shortcut on this, but I'm guessing there isn't. We have a request to pull a years worth of top usage data by due date.
>
> So we have utility bills by customer and due date. We want to pull all customers that have the top usage by each billing cycle. I know how to pull that for all records, is there a shortcut way to pull the top x by a group by?
>
> I am thinking like
> SELECT CUSTOMER, DUE_DATE, TOP100(USAGE) FROM BILLHIST GROUP BY CUSTOMER, DUE_DATE
>
> If there isn't, I guess I'll have to get creative. I'd build a program, but we only need it a couple times until May when this software will be done and we'll be on a .NET platform.
>
> --
> Mike Wills
> http://mikewills.me
> Ph: 507-933-0880
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com