SQL Querying with Column's and Data Issue

Ignition
,
1

I’m running into a problem where I have a column that catches a fault number on a machine when it happens. There’s a couple of stations on the machine, meaning there’s a large range of possible fault code numbers. How would I display the top ten offenders in a report? I’m seeing through the “Show calculations” that there doesn’t seem to be something to fit that. Thanks so much in advance.

2

MS SQL:
select top 10 *
group by ‘fault_code’

MySQL:
Select *
group by ‘fault_code’
limit 10

only guidelines, you will need to tweak

3

Yeah, you rarely (never?) get away with wildcard columns with any form of grouping.

4

I have timestamps into a table, every 4 seconds or so when machine(s) is(are) running well, the following query groups as quantity per hour using a wildcard, albeit using a count function - but it’s still there :no_mouth:

SELECT datepart(hour, [TestTime]) as hour_offloaded, count(*) as qty FROM
where a
and b
and TestTime > ?
and TestTime < ?
group by datepart(hour, [TestTime])
order by datepart(hour, [TestTime])

1 Like
5

Using that as a parameter results in an N/A. Thanks for the thoughts though, I’ll be toying around with it.

6

You have to think like the database thinks. Build your query while mentally building your cursor. TOP, GROUP and others are tricky. TOP will take whatever is on top of the cursor. If you want to get the top 10 by count, first sort by count, then TOP it. Otherwise, you’ll get whatever is there by chance.

For example

Select top 10 IDField, Events from
(
Select IDField, count(*) Events from Table
where
group by IDField
order by Events desc
)

This way, you get your cursor grouped and ordered first, then you skim the top 10.
About your where, I don’t work much with SQL server but usually (in real database environments) a between clause works better than a pair of greater than/less than clause.

7

I generally avoid between clauses, as they include "equals" on both endpoints. When the actual storage precision of timestamps is unknown, and samples could be recorded within the last second of a time period, it is is best to use half-open segments. That is, specify the start time with greater-than-or-equal, and the end time with less-than. Then "end time" is the beginning of the next time period in this case, and guarantees that data points near the boundary will fall into one or the other.

8

You’re right, it wouldn’t work in this case.