-1

I have my result set as I want it, but I can't find out how to display only the first row in each group. In prod I'll be running 50 FACID at at time. I am attempting to find the lowest daily average message count from a week so I can set thresholds for alerting.

select mirth_channel, facility, DATE(received_on) as Day,
       round (count(*) / 24)  AS 'average'
from message
where facility in ('FACID1', 'FACID2', 'FACID3', 'FACID4', 'FACID5')
AND received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
group by DATE(received_on), facility
order by average asc;
O. Jones
  • 103,626
  • 17
  • 118
  • 172
Holland
  • 1
  • 1
  • Possible duplicate of [Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) – philipxy Jul 11 '22 at 20:40

1 Answers1

0

You can use MIN(val) as an aggregate function that will find the lowest "val" in each set created by the group by. You are using the group by to create sets of each facility on a specific day, so the MIN will find the lowest value of the specified column in each set.

Anees Mohideen
  • 16
  • I've attempted to use MIN in different locations without success. Where would I add MIN in this code? – Holland Jul 11 '22 at 18:03
  • Hello and thank you for your response. I am still uncertain where to place the MIN(val) function in this code. I've tried multiple ways and none seem to work – Holland Jul 13 '22 at 13:36