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?
Whatâs a perato ? Did you mean pareto ? What do you want the output to look like ?
1 Like
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.
each row is like
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:
column value
col134 71
col22 70
col23 65
col156 62
col111 60
to begin with
So for every column you want the max value, and then the order of the columns based on each max value is that right?
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
What have you tried so far? There are many examples on Mr. Internet.
If you see any example of this, let me know.
I donât see any.
That took 20 seconds. 
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
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
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
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
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
1 Like
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.
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.
2 Likes
The first link above had an unpivot example
Hmmm, yes. However, it doesn't retain the identity of the origin column. Can it?
2 Likes
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.
Is there an aversion to scripting this?
3 Likes
A stack overflow post suggests it can....
### 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
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]]
2 Likes