Create view and show max value - mimer sql

Question

I would like to create a view that presents name and number of a "department" with the max value.

drop view highestvalue;

CREATE VIEW HighestValue AS SELECT s.dept, d.name, SUM(s.quantity) TotalQuantity
FROM sale AS s
INNER JOIN dept AS d ON d.number = s.dept
GROUP BY s.dept, d.name
ORDER BY TotalQuantity;

select * from HighestValue;

Expected result:

Defined view with name and number of the department with most sold items

score 2 · Answer 1 · edited Jan 06 '19 at 14:23

2

If you want only one row, and your DBMS's version is 10.1+ then use fetch first:

CREATE VIEW HighestValue AS
    SELECT s.dept, d.name, SUM(s.quantity) TotalQuantity
    FROM sale s INNER JOIN
         dept d
         ON d.number = s.dept
    GROUP BY s.dept, d.name
    ORDER BY TotalQuantity
    FETCH FIRST 1 ROW ONLY;

edited Jan 06 '19 at 14:23

Barbaros Özhan

59,113
10
31
55

answered Jan 06 '19 at 14:12

Gordon Linoff

1,242,037
58
646
786

Im using mimer sql, that didnt do the work for me, doesnt give me highest value, i added desc and fetched the first row, but i dont think thats the best way to do it? – Boohz12 Jan 06 '19 at 14:49
@Boohz12 . . . http://download.mimer.com/pub/developer/docs/html_101/Mimer_SQL_Engine_DocSet/select_spec.html. – Gordon Linoff Jan 06 '19 at 14:51

score 0 · Answer 2 · edited Apr 16 '20 at 22:51

0

You may do like this

CREATE VIEW HighestValue AS SELECT s.dept, COUNT(d.number) totalDepartments, SUM(s.quantity) TotalQuantity
FROM sale AS s
INNER JOIN dept AS d ON d.number = s.dept
GROUP BY s.dept
ORDER BY TotalQuantity;

edited Apr 16 '20 at 22:51

Dharman

30,962
25
85
135

answered Jan 06 '19 at 15:13

Delickate

1,102
1
11
17

Create view and show max value - mimer sql

2 Answers2