Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (11g)
SQL Query [message #666913] Fri, 01 December 2017 02:39 Go to next message
LKT
Messages: 5
Registered: November 2017
Junior Member
Hi,

I need to wright a query to find a max value in table.
My table is having 250 columns.

Its difficult to wright a max for every column.

Could you please help me to do this?

Thanks in Advance.
Re: SQL Query [message #666914 is a reply to message #666913] Fri, 01 December 2017 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How is it difficult?

Re: SQL Query [message #666922 is a reply to message #666913] Fri, 01 December 2017 06:23 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
LKT wrote on Fri, 01 December 2017 02:39
Hi,

I need to wright a query to find a max value in table.
My table is having 250 columns.

Its difficult to wright a max for every column.

Could you please help me to do this?

Thanks in Advance.
What's so difficult about it?


select max(col_a),
       max(col_b),
       max(col_c)
from my_table;

So you have 250 columns instead of 3. So you have to write more lines of code. Sure, it's a bit tedious, but if you had started when you posted your question, you'd have been done three hours ago. Facility with any competent text editor (or even a lame editor like notepad) would make the repetition easier.

[Updated on: Fri, 01 December 2017 06:24]

Report message to a moderator

Re: SQL Query [message #666936 is a reply to message #666913] Fri, 01 December 2017 17:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You could generate the query dynamically:
SCOTT@orcl_12.1.0.2.0> declare
  2    v_sql  clob;
  3  begin
  4    v_sql := 'select ';
  5    for i in
  6  	 (select column_name
  7  	  from	 user_tab_columns
  8  	  where  table_name = 'DEPT')
  9    loop
 10  	 v_sql := v_sql || 'max("' || i.column_name || '"),';
 11    end loop;
 12    v_sql := rtrim (v_sql, ',') || ' from dept;';
 13    dbms_output.put_line (v_sql);
 14  end;
 15  /
select max("LOC"),max("DNAME"),max("DEPTNO") from dept;

PL/SQL procedure successfully completed.

then copy and paste the query and run it:
SCOTT@orcl_12.1.0.2.0> select max("DEPTNO"),max("DNAME"),max("LOC") from dept;

MAX("DEPTNO") MAX("DNAME")   MAX("LOC")
------------- -------------- -------------
           40 SALES          NEW YORK

1 row selected.
Re: SQL Query [message #666940 is a reply to message #666936] Sat, 02 December 2017 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the contest "how can I poke my eye" here are some examples.
SQL> set feed off head off sqlt off sqlbl on
SQL> select '
  2  select ' ||
  3         rtrim(xmlagg(xmlelement(c, 'max('||column_name||'),')).extract('//text()'),',') ||
  4         ' from dept;
  5
  6  '
  7  from user_tab_columns
  8  where table_name='DEPT'
  9  /

select max(DEPTNO),max(DNAME),max(LOC) from dept;

SQL> with
  2    core as (
  3      select substr(sys_connect_by_path(c,','),2) c
  4      from (select 'max('||column_name||')' c, column_id id
  5            from user_tab_columns where table_name='DEPT')
  6      where connect_by_isleaf = 1
  7      connect by prior id = id - 1
  8      start with id = 1
  9    )
 10  select '
 11  select '||c||' from dept;
 12
 13  '
 14  from core
 15  /

select max(DEPTNO),max(DNAME),max(LOC) from dept;

SQL> with
  2    cols as (
  3      select 'max('||column_name||')' c, column_id id
  4      from user_tab_columns
  5      where table_name='DEPT'
  6    ),
  7    core(c, id) as (
  8      select c, id from cols where id = 1
  9      union all
 10      select core.c||','||cols.c, cols.id
 11      from core, cols
 12      where cols.id = core.id + 1
 13    )
 14  select '
 15  select '||max(c)||' from dept;
 16
 17  '
 18  from core
 19  /

select max(DEPTNO),max(DNAME),max(LOC) from dept;

SQL> select '
  2  select '||max(r)||' from dept;
  3
  4  '
  5  from (
  6  select r
  7  from ( select 'max('||column_name||')' c, column_id id
  8         from user_tab_columns
  9         where table_name='DEPT' )
 10  model
 11    return updated rows
 12    dimension by (id)
 13    measures (cast(null as varchar2(100)) r, c)
 14    rules ( r[any] order by id = decode(r[1], null, '', r[cv()-1]||',') || c[cv()] )
 15  )
 16  /

select max(DEPTNO),max(DNAME),max(LOC) from dept;
Re: SQL Query [message #666947 is a reply to message #666940] Sat, 02 December 2017 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

One step further:
SQL> create or replace function get_table_max (p_table in varchar2)
  2  return sys.odcivarchar2list
  3  pipelined
  4  is
  5    cols sys.odcivarchar2list;
  6    stmt varchar2(32767);
  7    res  clob;
  8  begin
  9    select column_name
 10    bulk collect into cols
 11    from user_tab_columns
 12    where table_name = dbms_assert.sql_object_name(p_table);
 13    if cols.count > 0 then
 14       for i in cols.first..cols.last loop
 15         stmt := stmt || '''
 16  '|| rpad(cols(i),30,'.') ||' ''||max('||cols(i)||')||';
 17       end loop;
 18       stmt := 'select substr(' || rtrim(stmt,'|') || ',2) ' ||
 19               'from ' || dbms_assert.sql_object_name(p_table);
 20      execute immediate stmt into res;
 21    else
 22      res := 'Unknow table "'||dbms_assert.sql_object_name(p_table)||'"';
 23    end if;
 24    pipe row (res);
 25  end;
 26  /

Function created.

SQL> sho err
No errors.
SQL> select column_value MAX_OF_DEPT from table(get_table_max('DEPT'));
MAX_OF_DEPT
---------------------------------------------------------------------------------
DEPTNO........................ 40
DNAME......................... SALES
LOC........................... NEW YORK
Re: SQL Query [message #666948 is a reply to message #666947] Sat, 02 December 2017 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another step:
SCOTT> create or replace function get_table_max (p_table in varchar2)
  2  return sys.odcivarchar2list
  3  pipelined
  4  is
  5    cols sys.odcivarchar2list;
  6    stmt varchar2(32767);
  7    res  clob;
  8  begin
  9    select column_name
 10    bulk collect into cols
 11    from user_tab_columns
 12    where table_name = dbms_assert.sql_object_name(p_table);
 13    if cols.count > 0 then
 14       for i in cols.first..cols.last loop
 15         stmt := stmt || '''
 16  '|| rpad(cols(i),30,'.') ||' ''||max('||cols(i)||')||';
 17       end loop;
 18       stmt := 'select substr(' || rtrim(stmt,'|') || ',2) ' ||
 19               'from ' || dbms_assert.sql_object_name(p_table);
 20      execute immediate stmt into res;
 21    else
 22      res := 'Unknow table "'||dbms_assert.sql_object_name(p_table)||'"';
 23    end if;
 24    pipe row (res);
 25  end;
 26  /

Function created.

SCOTT> sho err
No errors.
SCOTT> create or replace function get_tables_max
  2  return sys.odcivarchar2list
  3  pipelined
  4  is
  5    cols sys.odcivarchar2list;
  6    stmt varchar2(32767);
  7    res  clob;
  8  begin
  9    for tab in (select table_name from user_tables order by 1)
 10    loop
 11      pipe row ('');
 12      pipe row ('MAX OF '||tab.table_name);
 13      pipe row (rpad('-',length(tab.table_name)+7,'-'));
 14      select column_value into res from table(get_table_max(tab.table_name));
 15      pipe row (res);
 16    end loop;
 17  end;
 18  /

Function created.

SCOTT> sho err
No errors.
SCOTT> set head off arrays 1
SCOTT> select * from table(get_tables_max());

MAX OF BONUS
------------
ENAME.........................
JOB...........................
SAL...........................
COMM..........................

MAX OF DEPT
-----------
DEPTNO........................ 40
DNAME......................... SALES
LOC........................... NEW YORK

MAX OF EMP
----------
EMPNO......................... 7934
ENAME......................... WARD
JOB........................... SALESMAN
MGR........................... 7902
HIREDATE...................... 23/05/1987 00:00:00
SAL........................... 5000
COMM.......................... 1400
DEPTNO........................ 30

MAX OF SALGRADE
---------------
GRADE......................... 5
LOSAL......................... 3001
HISAL......................... 9999
Now you have to remove from the column list those with a datatype that can't have a MAX like LONG, LOB, XMLTYPE.

[Updated on: Sat, 02 December 2017 10:36]

Report message to a moderator

Re: SQL Query [message #666950 is a reply to message #666948] Sat, 02 December 2017 19:45 Go to previous messageGo to next message
LKT
Messages: 5
Registered: November 2017
Junior Member
Thanks....
Re: SQL Query [message #666955 is a reply to message #666950] Sun, 03 December 2017 04:00 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That "thanks...." sounds as if you're really, really resigned. As if you regret for asking the question. Maybe you hoped for an effortless option (such as "SELECT GIMME_ALL_MAXES(all_columns) FROM my_table") and got yourself caught in disbelief when seeing all those magnificent options.

I don't know about you, but I'd throw rose petals where such people (Barbara, Michel & Co.) walk, saying thankyouthankyouthankyou!. But that's OK, we don't share the same enthusiasm. At least, you did thank people who assisted (unlike many others do).

By the way, which option did you choose to solve your problem? Was it Ed's? I'd put my bet on that.
Re: SQL Query [message #666969 is a reply to message #666955] Mon, 04 December 2017 02:26 Go to previous messageGo to next message
LKT
Messages: 5
Registered: November 2017
Junior Member
Hi ,

I am sorry if I hurt you.
I am new to this technology and trying to learn a lot from seniors.

You all seniors have given provided me a way, how to develop a thinking to resolve such a issues.

Thanks once again.

The last solution provided by a Michel Cadot resolved my problem around 80%.

Once again Thanks to all seniors.

Regards,
LT
Re: SQL Query [message #666970 is a reply to message #666969] Mon, 04 December 2017 02:41 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, no; I'm fine, thank you (and so is everyone else, I hope). Though, I'd lose my money (wrong bet, eh) but I'm glad you found those examples useful.

If I may ask, how come that you managed to solve 80% of the problem? What about the rest of it?
Re: SQL Query [message #666972 is a reply to message #666970] Mon, 04 December 2017 03:02 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What about the rest of it?
Exactly what I wanted to post.
Post a complete description of your actual problem so we can provide a more accurate, efficient and reliable solution than those I have posted (and which have many drawbacks).

Previous Topic: Difference in SQL 'order by' and MIN, MAX, LISTAGG results
Next Topic: remove duplicate values by date
Goto Forum:
  


Current Time: Thu Mar 28 09:02:04 CDT 2024