MAX Function without Group By Statement

[Baron]

I have a general SQL Question, and hope that I can find an answer here:

Example:

create or replace table Employees (emp_id int, firstname varchar(10), lastname varchar(10));

create or replace table Projects (emp_id int, project_name varchar(10));

insert into employees values

('1', 'James', 'smith'),

('2', 'Michael', 'Smith'),

('3', 'Maria', 'Garcia');

insert into Projects values

('1', 'Project1'),

('2', 'Project2'),

('2', 'Project3');

create or replace procedure myproc ()

begin

select e.emp_id, firstname, lastname, project_name from Employees e, Projects p where e.emp_id =p.emp_id;

end;

What I need is to call myproc and also get the max(emp_id).

My first try:

select (select max(emp_id) from myproc()), * from myproc()

Drawback: myproc will be called 2 times

Is there a way to get the same result with avoiding the double call of myproc

[VolkerBarth]

Is this question focusing on using a procedure or more generally on how to get both all (or some) rows of a table/join and additionally an aggregated value? And do you really want the result set to contain all values from the join or just some of them (or just the one with the max emp_id)? As usual, a sample result set would be helpful.

[Baron]

I have already the procedure myproc (written since years) which is pretty complicated and returns big amount of data (several thousands of rows * several tens of columns).

I am using this myproc to build a report, and now decided to add some header data to the report (resulting from aggregate functions on some of the existing columns).

Since I dont want to change the procedure itself, I decided to go with this approach.

So, to your question, yes I want really the result set to contain all values.

[VolkerBarth]

Well, then Breck's approach with WINDOW functions should be fine for you - or build a wrapper procedure that selects that procedure's result set into a temporary table, does some more calculations/aggregating on the latter and selects from the temporary table.

[Baron]

Since I need to make different aggregate functions on different columns, then the best solution for me is with temp table and wrapper procedure (Despite I avoid always using temp tables).

BTW, my first try has another big drawback:

select (select max(emp_id) from myproc()), * from myproc()

Since two consequent calls on the same procedure could deliver different results.

[Baron]

@Volker, BTW, what is the difference (from performance point of view) between writing the results of the procedure in a temp table, and declaring a cursor on the result set of the procedure?

[VolkerBarth]

I don't know - but I suggest to ask that as a separate question...

[Breck_Carter · Accepted solution]

Question: How do I write a query that makes no sense?

Answer: Use a feature that makes no sense, such as LATERAL or WINDOW:)

In this case LATERAL doesn't seem to help, but brute-force WINDOW seems to work...

select max ( emp_id ) over all_rows_window as max_emp,
       *
  from myproc()
window all_rows_window as ( rows between unbounded preceding and unbounded following );

    max_emp      emp_id firstname  lastname   project_name 
----------- ----------- ---------- ---------- ------------ 
          2           1 James      smith      Project1     
          2           2 Michael    Smith      Project2     
          2           2 Michael    Smith      Project3     

---

Volker's suggestion of OVER() makes the code shorter...

select min ( emp_id ) over() as min_emp,
       max ( emp_id ) over() as max_emp,
       min ( firstname ) over() as min_firstname,
       max ( firstname ) over() as max_firstname,
       min ( lastname ) over() as min_lastname,
       max ( lastname ) over() as max_lastname,
       min ( project_name ) over() as min_project_name,
       max ( project_name ) over() as max_project_name,
       *
  from myproc();

    min_emp     max_emp min_firstname max_firstname min_lastname max_lastname min_project_name max_project_name      emp_id firstname  lastname   project_name 
----------- ----------- ------------- ------------- ------------ ------------ ---------------- ---------------- ----------- ---------- ---------- ------------ 
          1           2 James         Michael       smith        smith        Project1         Project3                   1 James      smith      Project1     
          1           2 James         Michael       smith        smith        Project1         Project3                   2 Michael    Smith      Project2     
          1           2 James         Michael       smith        smith        Project1         Project3                   2 Michael    Smith      Project3     

[VolkerBarth · Accepted solution]

Well, if the query really must avoid using the underlying tables and must not call the stored procedure twice, I guess a WINDOW function is the way to go. Note, you can omit the WINDOW clause here as the default window sizes are identical to "rows between unbounded preceding and unbounded following" (but omitting that may be less comprehensible).

select max(emp_id) over () as max_emp, *
from myproc();

[Vlad · Accepted solution]

I liked the way how you rephrased the question 🙂

[Baron · Accepted solution]

Thanks for the help, but the answer from @Volker would help better in my case.

But nice to to know about WINDOW function (it is new for me).

[Breck_Carter · Accepted solution]

With OVER() you can calculate MIN, MAX, etc, on all the columns all at once, with no GROUP BY, and only one call to myproc() is executed. The performance is probably excellent since all the rows are being retrieved anyway.

[Breck_Carter · Accepted solution]

Yeah, but... it was just the example that made no sense. The bigger question is "How do I use aggregate functions without the limitations imposed by GROUP BY?"

OVER() is just like leaving out the GROUP BY altogether by specifying a "WINDOW over everything".

I think I've written a lot of GROUP BY queries that would be simpler with OVER().

[Breck_Carter · Accepted solution]

> may be less comprehensible

No, no, it's brilliant! You're thinking outside the window! 🙂

The OVER() clause opens up a whole realm of new possibilities! Any day without a GROUP BY is a good day!

[Baron · Accepted solution]

@breck carter, thanks again, it worth to go with OVER().

[former_SQLA_member1694998]

You are really trying to get two pieces of unrelated information in one query...

Me? I would do it like this

select * from myproc() Order By emp_id DESC

The very first row will be the Max emp_id .. If your report is custom, pick it out and use it in your report... If you are using a commercial reporting tool it probably has functions to pick out the MAX or First as well...

[Baron]

Yes your approach is OK, but my example was very simple (compared to my real case).

In reality the procedure is much more bigger, and I have to make aggregate function on more than one column (not only on emp_id).

Moreover, I am requested to deliver Max/Min values for some columns (so order by can not be the solution).

[Breck_Carter]

> aggregate function on more than one column

See update to earlier WINDOW answer.

[jack_schueler]

I am no SQL wizard, but did you mean something like this? Or are you trying to avoid two queries in myproc?

create or replace procedure myproc ( out max_id int )
begin
    select max(e.emp_id) into max_id from Employees e, Projects p where e.emp_id = p.emp_id;
    select e.emp_id, firstname, lastname, project_name from Employees e, Projects p where e.emp_id = p.emp_id;
end;

BEGIN 
    declare my_max int;
    select * from myproc( my_max );
    select my_max;
END

[VolkerBarth]

Well, "SQL Wizards" would probably omit procedural logic and use joins and derived tables and probably a local view (common table expression), such as:

with CTE as
   (select e.emp_id, firstname, lastname, project_name
    from Employees e inner join Projects p on e.emp_id = p.emp_id)
select CTE_Max.*, CTE.*
from (select max(emp_id) as max_emp from CTE) CTE_Max
   cross join CTE

And IMVHO for most queries selecting some "max row" result set, one would not use a CROSS JOIN to list a max value and all rows but only those rows who fit the max value, so using something like "inner join CTE on max_emp = CTE.emp_id" or the like...:)

[jack_schueler]

Dang! In a previous life, you probably wrote APL code. 🙂

[VolkerBarth]

No, I had to look up what APL is - but I guess I got the point:)

[Baron]

@JBSchueler, the point is that I dont want to change the original procedure (which is quite complicated enough). I want to keep the same procedure and use its result, so decided to go with a wrapper procedure and a temp table.

Thanks