# Sql pareto for column values per row help request **URL:** \ **Category:** Ignition\ **Created:** [May 2, 2022, 2:51pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119 "2022-05-02T14:51:11Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 2:51pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/1 "2022-05-02T14:51:11Z")
This table has about 200 columns of counters that are recorded at the end of each shift. It has about 4 other columns for some meta data. How would a top 5 Pareto be made from such a table? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [May 2, 2022, 3:05pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/2 "2022-05-02T15:05:43Z")
What’s a perato ? Did you mean pareto ? What do you want the output to look like ? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [May 2, 2022, 3:06pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/3 "2022-05-02T15:06:56Z")
`SELECT TOP 5 *` and then an appropriate `ORDER BY` that is measuring the metric’s that determine the top 5. Without more information as to how you are ranking each row, it’s hard to give any more information. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 3:13pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/4 "2022-05-02T15:13:35Z")
each row is like ```auto col1 col2 ......col200 t_stamp color area 3 34 6 (tstamp) blue zoneA ``` I want the output to show the top 5 column values and the column name like: ```auto column value col134 71 col22 70 col23 65 col156 62 col111 60 ``` to begin with ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [May 2, 2022, 3:28pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/5 "2022-05-02T15:28:31Z")
So for every column you want the max value, and then the order of the columns based on each max value is that right? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 3:32pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/6 "2022-05-02T15:32:18Z")
to begin with, I need the top 5 columns from row 1 based on max value in those columns for that row Eventually, I need to get top 5 columns from row 1 then top 5 columns from row 2 then top 5 columns rom row x ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [May 2, 2022, 3:35pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/7 "2022-05-02T15:35:35Z")
What have you tried so far? There are many examples on Mr. Internet. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 3:35pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/8 "2022-05-02T15:35:57Z")
If you see any example of this, let me know. I don’t see any. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [May 2, 2022, 3:38pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/9 "2022-05-02T15:38:12Z")
> **[Find MAX value from multiple columns in a SQL Server table](https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/)** > > In this article we look at different T-SQL approaches to get the maximum value from multiple columns in a table. > > **[Getting MAX of multiple columns in SQL Server | My Tec Bits](https://www.mytecbits.com/microsoft/sql-server/getting-max-of-multiple-columns)** > > Here is simple method to find the MAX of multiple columns in SQL Server. Getting smallest or highest values from multiple columns. That took 20 seconds. 🙂 ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 3:40pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/10 "2022-05-02T15:40:07Z")
I might be able to `DECLARE @TB TABLE(Column1 NVARCHAR(50),…) INSERT @TB SELECT \* FROM student Then use it: SELECT Column1 FROM @TB WHERE Column1=‘aa’` and set some max out of that ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 3:41pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/11 "2022-05-02T15:41:59Z")
@jlandwerlen why do you post if just posting google search and not actually helping? You want me to look at all the ways it will not work? I will be back after lunch, going to spend 2 hours looking at these ways that nobody knows if they work ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [May 2, 2022, 3:42pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/12 "2022-05-02T15:42:45Z")
I don’t think these links are quite what he wants though based on the example he gave before. This is like getting the max between column1,2,3 and returning that to the name of the row. He wants the name of the column and the max value of the column, and then sorting them. I haven’t used them in forever but I think this might be the time for a PIVOT table. Personally I would probably just hard code the query once ```auto SELECT max(column1) as 'column1', max(column2) as 'column2', etc.... ``` Which would be annoying but you could probably use NotePad++ to make it easier and then you’d have half of your work done. Then its just a matter of sorting. Though then again, this will be in ```auto column1 column2 column3 etc 111 14 57 etc ``` but if this is in scripting you can use jython to do what you need to it from here I think 🙂 ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [May 2, 2022, 3:45pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/13 "2022-05-02T15:45:05Z")
My approach at helping is maybe a bit different compared to others. I’m in the camp that believes you should make an effort at something before asking for the answer. I didn’t go through each link, just showing how easy it is to search for help on SQL. I’ll leave it at that. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 2, 2022, 3:47pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/14 "2022-05-02T15:47:50Z")
This requires an “UNPIVOT”, then a rank() window aggregate, finally filtering and ordering by that rank. I have to admit not knowing how to unpivot short of a script. But that is the search term I would put into google. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [May 2, 2022, 3:50pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/15 "2022-05-02T15:50:19Z")
The first link above had an unpivot example ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 2, 2022, 3:53pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/16 "2022-05-02T15:53:56Z")
> [@jlandwerlen](#): > > unpivot example Hmmm, yes. However, it doesn't retain the identity of the origin column. Can it? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [May 2, 2022, 3:56pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/17 "2022-05-02T15:56:50Z")
I think its fair of you to ask to see an attempt/effort but also fair to point out that your links didn’t match what he had given as his desired outputs. Posting links for links sakes doesn’t really prove anything or help anyone. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [May 2, 2022, 4:29pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/18 "2022-05-02T16:29:19Z")
Is there an aversion to scripting this? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 2, 2022, 4:33pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/19 "2022-05-02T16:33:40Z")
> [@pturmel](#): > > retain the identity of the origin column. Can it? A stack overflow post suggests it can.... ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [May 2, 2022, 4:45pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/20 "2022-05-02T16:45:29Z")
```python ### Sample Dataset to simulate query result import random headers = ['col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col10', 't_stamp', 'color', 'area'] data = [] t_stamp = system.date.now() for i in range(10): newRow = [random.randrange(100) for j in range(10)] t_stamp = system.date.addMinutes(t_stamp, 1) newRow.extend([t_stamp, 'blue', 'zoneX']) data.append(newRow) dataIn = system.dataset.toPyDataSet(system.dataset.toDataSet(headers, data)) #util.printDataSet(dataIn) print '---' ### Sorting # List of exluded columns excluded_cols = ['t_stamp', 'color', 'area'] # Get columns to use in sorting filteredHeaders = [colName for colName in dataIn.getColumnNames() if colName not in excluded_cols] for row in dataIn: data = [row[colName] for colName in filteredHeaders] #Sorting a zip sorts all lists by the first one. pareto = [[x, y] for y, x in reversed(sorted(zip(data, filteredHeaders)))][:5] print row['t_stamp'], pareto ``` ```nohighlight row | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | t_stamp | color | area ------------------------------------------------------------------------------------------------------------------------- 0 | 99 | 40 | 49 | 0 | 70 | 32 | 76 | 69 | 70 | 33 | Mon May 02 12:39:36 EDT 2022 | blue | zoneX 1 | 75 | 81 | 77 | 51 | 49 | 93 | 62 | 15 | 59 | 53 | Mon May 02 12:40:36 EDT 2022 | blue | zoneX 2 | 45 | 7 | 39 | 63 | 74 | 31 | 29 | 8 | 82 | 44 | Mon May 02 12:41:36 EDT 2022 | blue | zoneX 3 | 31 | 39 | 57 | 72 | 79 | 16 | 17 | 57 | 48 | 9 | Mon May 02 12:42:36 EDT 2022 | blue | zoneX 4 | 1 | 24 | 68 | 57 | 66 | 92 | 54 | 72 | 46 | 67 | Mon May 02 12:43:36 EDT 2022 | blue | zoneX 5 | 53 | 64 | 5 | 60 | 55 | 93 | 64 | 96 | 66 | 63 | Mon May 02 12:44:36 EDT 2022 | blue | zoneX 6 | 41 | 24 | 23 | 82 | 50 | 89 | 77 | 74 | 99 | 27 | Mon May 02 12:45:36 EDT 2022 | blue | zoneX 7 | 4 | 40 | 24 | 56 | 3 | 17 | 85 | 74 | 68 | 53 | Mon May 02 12:46:36 EDT 2022 | blue | zoneX 8 | 33 | 16 | 64 | 55 | 76 | 64 | 21 | 44 | 13 | 14 | Mon May 02 12:47:36 EDT 2022 | blue | zoneX 9 | 79 | 31 | 46 | 53 | 1 | 8 | 35 | 63 | 69 | 14 | Mon May 02 12:48:36 EDT 2022 | blue | zoneX --- Mon May 02 12:39:36 EDT 2022 [[u'col1', 99], [u'col7', 76], [u'col9', 70], [u'col5', 70], [u'col8', 69]] Mon May 02 12:40:36 EDT 2022 [[u'col6', 93], [u'col2', 81], [u'col3', 77], [u'col1', 75], [u'col7', 62]] Mon May 02 12:41:36 EDT 2022 [[u'col9', 82], [u'col5', 74], [u'col4', 63], [u'col1', 45], [u'col10', 44]] Mon May 02 12:42:36 EDT 2022 [[u'col5', 79], [u'col4', 72], [u'col8', 57], [u'col3', 57], [u'col9', 48]] Mon May 02 12:43:36 EDT 2022 [[u'col6', 92], [u'col8', 72], [u'col3', 68], [u'col10', 67], [u'col5', 66]] Mon May 02 12:44:36 EDT 2022 [[u'col8', 96], [u'col6', 93], [u'col9', 66], [u'col7', 64], [u'col2', 64]] Mon May 02 12:45:36 EDT 2022 [[u'col9', 99], [u'col6', 89], [u'col4', 82], [u'col7', 77], [u'col8', 74]] Mon May 02 12:46:36 EDT 2022 [[u'col7', 85], [u'col8', 74], [u'col9', 68], [u'col4', 56], [u'col10', 53]] Mon May 02 12:47:36 EDT 2022 [[u'col5', 76], [u'col6', 64], [u'col3', 64], [u'col4', 55], [u'col8', 44]] Mon May 02 12:48:36 EDT 2022 [[u'col1', 79], [u'col9', 69], [u'col8', 63], [u'col4', 53], [u'col3', 46]] ``` [Next page](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119.md?page=2)