0

I'm working on finding a way to get all the max values from all the columns from multiple tables in the database. So far i have done this query:

SELECT
source,
max(column1), 
max(column2), 
max(column3), 
max(column4), 
max(column5), 
FROM database.table
WHERE date_recent = '2019-01-01'
GROUP BY source;

Ofcourse this works for one table and is a bit tedious as i had to select each column manually, how could i approach this?

Please bear with me as I'm new to SQL, any help would be appreciated, thank you.

AmsKumar
  • 93
  • 1
  • 10
  • 1
    I'll keep my clothes on, thanks. See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) - although this kind of problem *can* be symptomatic of poor/sub-optimal deisgn. – Strawberry Dec 04 '19 at 15:17
  • Thank you for your input :) – AmsKumar Dec 04 '19 at 15:20
  • Multiple tables? Some inspiration [here](https://stackoverflow.com/a/29248442/4003419). But all columns of each table? Ouch... Well, I guess one could build a dynamic sql based on the meta data from [INFORMATION_SCHEMA.COLUMNS](https://dev.mysql.com/doc/refman/8.0/en/columns-table.html). – LukStorms Dec 04 '19 at 15:28

2 Answers2

1

Unfortunately I don't think that what you're looking for is possible in MySQL, you'll need to write the query such that it selects the MAX from each column individually. However if you do have a lot of tables with a lot of columns and it'll end up being a long query, you could try automating it with a script to generate the query for you automatically by using "SHOW COLUMNS IN table" and then building the query from the list of columns.

Edit: You could also expand on this to make it generate automatically for every single table if you need it by using "SHOW TABLES" and then iterating through those results with the "SHOW COLUMNS IN tableName" being inside that loop.

tkingston
  • 114
  • 6
  • Hi, thanks for your answer... What you're saying makes complete sense, ideally that is the approach i want to take, could you please guide me with how to make the script? – AmsKumar Dec 04 '19 at 15:40
0

You can use something like this in MySQL

SELECT GREATEST(col1, col2 ...) FROM table

but it is in MySql and you have tagged it.

Its_Ady
  • 308
  • 2
  • 10
  • This might need a little clarification from OP - GREATEST will only return which column has the highest value, it won't return the data row with the highest value from a column. – tkingston Dec 04 '19 at 15:41
  • Yes, but i need to do it for multiple tables, so this will still be a lengthy approach as every column is different – AmsKumar Dec 04 '19 at 15:41
  • Sadly `GREATEST` returns `NULL` if any of the values is null. So will require a lot of `COALESCE` to cope with that. – LukStorms Dec 04 '19 at 15:52