MAX of SUM and use of TOP in Views

Question

I have the following tables in SQL Server:

• COMMANDLINES: ID_LINE - ID_COMMAND - ID_ARTICLE - QUANTITY
• COMMAND: ID_COMMAND - ID_CLIENT - PRICE - PRINTED
• CLIENT: ID_CLIENT - FULL_NAME - SSN - PH_NUM - MOBILE - USERNAME - PASSWORD

• ARTICLE: ID_ARTICLE - DES - NAME - PRICE - TYPE - CURRENT_QTT - MINIMUM_QTT

• ID_COMMAND from COMMANDLINES references COMMAND.ID_COMMAND

• ID_CLIENT from COMMAND references CLIENT.ID_CLIENT
• ID_ARTICLE from COMMANDLINES references ARTICLE.ID_ARTICLE

I need to create a view where I need to show all COMMANDLINES that have the best client (the one with the highest total of PRICE) and then I need to order them by ID_COMMAND in a descending order AND by ID_LINE in ascending order.

Sample data:

COMMANDLINE table:

COMMAND table:

Only these 2 are needed to resolve the problem. I added the other just for more information.

Sample output:

To be honest, I'm not sure if both outputs are supposed to be "output" at the same time or that I need 2 VIEWS for each output.

WHAT HAVE I DONE SO FAR:

I looked through what I could find on StackOverflow about MAX of SUM, but unfortunately, it has not helped me much in this case. I always seem to be doing something wrong.

I also found out that in order to use ORDER BY in VIEWS you need to, in this case, use TOP, but I've no idea how to apply it correctly when I need to select all of the COMMANDLINES. In one of my previous things, I used the following SELECT TOP:

create view PRODUCTS_BY_TYPE
as
    select top (select count(*) from ARTICLE 
                where CURRENT_QTT > MINIMUM_QTT)*
    from 
        ARTICLE
    order by 
        TYPE

This allowed me to show all PRODUCT data where the CURRENT_QTT was more than the minimum ordering them by type, but I can't figure out for the life of me, how to apply this to my current situation.

I could start with something like this:

create view THE_BEST
as
    select COMMANDLINE.*
    from COMMANDLINE

But then I don't know how to apply the TOP.

I figured that first, I need to see who the best client is, by SUM-ing all of the PRICE under his ID and then doing a MAX on all of the SUM of all clients.

So far, the best I could come up with is this:

create view THE_BEST
as
    select top (select count(*) 
                from (select max(max_price) 
                      from (select sum(PRICE) as max_price 
                            from COMMAND) COMMAND) COMMAND) COMMANDLINE.*
    from COMMANDLINE
    inner join COMMAND on COMMANDLINE.ID_COMMAND = COMMAND.ID_COMMAND
    order by COMMAND.ID_COMMAND desc, COMMANDLINE.ID_LINE asc

Unfortunately, in the select count(*) the COMMAND is underlined in red (a.k.a. the 3rd COMMAND word) and it says that there is "no column specified for column 1 of COMMAND".

EDIT:

I've come up with something closer to what I want:

create view THE_BEST
    as
        select top (select count(*) 
                    from (select max(total_price) as MaxPrice 
                          from (select sum(PRICE) as total_price 
                                from COMMAND) COMMAND) COMMAND)*
        from COMMANDLINE
        order by ID_LINE asc

Still missing the ordered by ID_COMMAND and I only get 1 result in the output when it should be 2.

Answer 1

here is some code that hopefully will show you how you can use the top-clause and also a different approche to show only the "top" :-)

/* Creating Tables*/
CREATE TABLE ARTICLE (ID_ARTICLE int,DES varchar(10),NAME varchar(10),PRICE float,TYPE int,CURRENT_QTT int,MINIMUM_QTT int)
CREATE TABLE COMMANDLINES (ID_LINE int,ID_COMMAND int,ID_ARTICLE  int,QUANTITY int)
CREATE TABLE COMMAND (ID_COMMAND int, ID_CLIENT varchar(20), PRICE float, PRINTED int)
CREATE TABLE CLIENT (ID_CLIENT varchar(20), FULL_NAME varchar(50), SSN varchar(50), PH_NUM varchar(50), MOBILE varchar(50), USERNAME varchar(50), PASSWORD varchar(50))

INSERT INTO COMMANDLINES VALUES (1,1,10,20),(2,1,12,3),(3,1,2,21),(1,2,30,2),(2,2,21,5),(1,3,32,20),(2,3,21,2)
INSERT INTO COMMAND VALUES (1,'1695152D',1200,0),(2,'1695152D',500,0),(3,'2658492D',200,0)
INSERT INTO ARTICLE VALUES(1, 'A','AA',1300,0,10,5),(2,'B','BB',450,0,10,5),(30,'C','CC',1000,0,5,5),(21,'D','DD',1500,0,5,5),(32,'E','EE',1600,1,4,5),(3,'F','FF',210,2,15,5)
INSERT INTO CLIENT VALUES ('1695152D', 'DoombringerBG', 'A','123','321','asdf','asf'),('2658492D', 'tgr', 'A','123','321','asdf','asf')
GO
/* Your View-Problem*/
CREATE VIEW PRODUCTS_BY_TYPE AS 
SELECT TOP 100 PERCENT *
FROM ARTICLE
WHERE CURRENT_QTT > MINIMUM_QTT -- You really don't want >= ??
ORDER BY [Type]
-- why do you need your view with an ordered output? cant your query order the data?
GO

OUTPUT:

ID_ARTICLE   | DES   | NAME  | PRICE | TYPE | CURRENT_QTT  | MINIMUM_QTT
-------------+-------+-------+-------+------+--------------+-------------
1            | A     | AA    | 1300  | 0    | 10           | 5
2            | B     | BB    | 450   | 0    | 10           | 5
3            | F     | FF    | 210   | 2    | 15           | 5

I hope this is what you were looking for :-)

-- your top customers
SELECT cli.FULL_NAME, SUM(c.PRICE)
FROM COMMANDLINES as cl
INNER JOIN COMMAND as c
on cl.ID_COMMAND = c.ID_COMMAND
INNER JOIN CLIENT as cli
on cli.ID_CLIENT = c.ID_CLIENT
GROUP BY cli.FULL_NAME
ORDER BY SUM(c.PRICE) DESC -- highest value first


SELECT * 
FROM (
    -- your top customers with a rank
    SELECT cli.FULL_NAME, SUM(c.PRICE) as Price, ROW_NUMBER() OVER (ORDER BY SUM(c.PRICE) DESC) AS RowN
    FROM COMMANDLINES as cl
    INNER JOIN COMMAND as c
    on cl.ID_COMMAND = c.ID_COMMAND
    INNER JOIN CLIENT as cli
    on cli.ID_CLIENT = c.ID_CLIENT
    GROUP BY cli.FULL_NAME
) as a
-- only the best :-)
where RowN = 1 
--considerations: what if two customers have the same value?

Output:

FULL_NAME       |Price    | RowN
----------------+---------+-------
DoombringerBG   | 4600    | 1

Regards tgr

===== EDITED =====

The syntax-corrention to your THE_BEST-View:

create view THE_BEST AS
SELECT TOP (
        SELECT count(*) as cnt
        FROM (
            SELECT max(max_price) as max_price
            FROM (
                SELECT sum(PRICE) AS max_price
                FROM COMMAND
                ) COMMAND
            ) COMMAND
        ) 
        cl.*
FROM COMMANDLINES  as  cl
INNER JOIN COMMAND as c
    ON cl.ID_COMMAND = c.ID_COMMAND
ORDER BY c.ID_COMMAND DESC
    ,cl.ID_LINE ASC

Without the OVER-Clause:

SELECT TOP 1 * 
FROM (
    -- your top customers with a rank
    SELECT cli.FULL_NAME, SUM(c.PRICE) as Price
    FROM COMMANDLINES as cl
    INNER JOIN COMMAND as c
    on cl.ID_COMMAND = c.ID_COMMAND
    INNER JOIN CLIENT as cli
    on cli.ID_CLIENT = c.ID_CLIENT
    GROUP BY cli.FULL_NAME
) as a
-- only the best :-)
ORDER BY Price DESC 

Your PRODUCTS_BY_TYPE without PERCENT:

CREATE VIEW PRODUCTS_BY_TYPE AS 
SELECT TOP (select 
SUM(p.rows)
from sys.partitions as p
inner join sys.all_objects as ao
on p.object_id = ao.object_id
where ao.name = 'ARTICLE'
and ao.type = 'U')
*
FROM ARTICLE
WHERE CURRENT_QTT > MINIMUM_QTT -- You really don't want >= ??
ORDER BY [Type]
go

but to be honest - i would never use such a query in production... i only posted this because you need it for studing purposes...

Answer 2

It is quite likely that there is some misunderstanding between you and your teacher. You can technically have ORDER BY clause in a view definition, but it never guarantees any order of the rows in the query that uses the view, such as SELECT ... FROM your_view. Without ORDER BY in the final SELECT the order of the result set is not defined. The order of rows returned to the client by the server is determined only by the final outermost ORDER BY of the query, not by the ORDER BY in the view definition.

The purpose of having TOP in the view definition is to limit the number of returned rows somehow. For example, TOP (1). In this case ORDER BY specifies which row(s) to return.

Having TOP 100 PERCENT in a view does nothing. It doesn't reduce the number of returned rows and it doesn't guarantee any specific order of returned rows.

---

Having said all that, in your case you need to find one best client, so it makes sense to use TOP (1) in a sub-query.

This query would return the ID of the best client:

SELECT 
    TOP (1) 
    -- WITH TIES
    ID_CLIENT
FROM COMMAND
GROUP BY ID_CLIENT
ORDER BY SUM(PRICE) DESC

If there can be several clients with the same maximum total price and you want to return data related to all of them, not just one random client, then use TOP WITH TIES.

Finally, you need to return lines that correspond to the chosen client(s):

create view THE_BEST
as
    SELECT
        COMMANDLINE.ID_LINE
        ,COMMANDLINE.ID_COMMAND
        ,COMMANDLINE.ID_ARTICLE
        ,COMMANDLINE.QUANTITY
    FROM
        COMMANDLINE
        INNER JOIN COMMAND ON COMMAND.ID_COMMAND = COMMANDLINE.ID_COMMAND
    WHERE
        COMMAND.ID_CLIENT IN
        (
            SELECT 
                TOP (1) 
                -- WITH TIES
                ID_CLIENT
            FROM COMMAND
            GROUP BY ID_CLIENT
            ORDER BY SUM(PRICE) DESC
        )
;

This is how the view can be used:

SELECT
    ID_LINE
    ,ID_COMMAND
    ,ID_ARTICLE
    ,QUANTITY
FROM THE_BEST
ORDER BY ID_COMMAND DESC, ID_LINE ASC;

Note, that ORDER BY ID_COMMAND DESC, ID_LINE ASC has to be in the actual query, not in the view definition.