How to display the record with the highest value i

问题:

I have 4 tables with the following structure:

Table artist:

artistID lastname firstname nationality dateofbirth datedcease

Table work:

workId title copy medium description artist ID

Table Trans:

TransactionID Date Acquired Acquistionprice datesold askingprice salesprice customerID workID

Table Customer:

customerID lastname Firstname street city state zippostalcode country areacode phonenumber email

First question is which artist has the most works of artsold and how many of the artist works have been sold.

My SQL query is this:

SELECT * From dtoohey.artist A1 
INNER JOIN 
(
    SELECT COUNT(W1.ArtistID) AS COUNTER, artistID  FROM dtoohey.trans T1
    INNER JOIN dtoohey.work W1
    ON W1.workid = T1.Workid
    GROUP BY W1.artistID
) TEMP1
ON TEMP1.artistID = A1.artistID
WHERE A1.artistID = TEMP1.artistId
ORDER BY COUNTER desc;

I am to get the whole table but I only want show only the first row which is the highest count how do I do that??

I have tried inserting WHERE ROWNUM <=1 but it shows artist ID with 1

qns 2 is sales of which artist's work have resulted in the highest average profit (i.e) the average of the profits made on each sale of worksby an artist), and what is that amount.

My SQL query is:

SELECT A1.artistid, A1.firstname FROM
(
    SELECT 
        (salesPrice - AcquisitionPrice) as profit, 
        w1.artistid as ArtistID 
    FROM dtoohey.trans T1
    INNER JOIN dtoohey.WORK W1
    on W1.workid = T1.workid
) TEMP1
INNER JOIN dtoohey.artist A1
ON A1.artistID = TEMP1.artistID
GROUP BY A1.artistid
HAVING MAX(PROFIT) = AVG(PROFIT);

I'm not able to execute it

I have tried query below but still not able to get it keep getting the error missing right parenthesis

SELECT A1.artistid, A1.firstname, TEMP1.avgProfit
FROM 
(
    SELECT 
        AVG(salesPrice - AcquisitionPrice) as avgProfit, 
        W1.artistid as artistid
    FROM dtoohey.trans T1
    INNER JOIN dtoohey.WORK W1
    ON W1.workid = T1.workid
    GROUP BY artistid
    ORDER BY avgProfit DESC
    LIMIT 1
) TEMP1
INNER JOIN dtoohey.artist A1
ON A1.artisid = TEMP1.artistid

回答1:

Sometimes ORA-00907: missing right parenthesis means exactly that: we have a left bracket without a matching right one. But it can also be thrown by a syntax error in a part of a statement bounded by parentheses.

It's that second cause here: LIMIT is a Mysql command which Oracle does not recognise. You can use an analytic function here:

SELECT A1.artistid, A1.firstname, TEMP1.avgProfit
FROM 
(
    select  artistid
            , avgProfit
            , rank() over (order by avgProfit desc) as rnk
    from (
        SELECT 
            AVG(salesPrice - AcquisitionPrice) as avgProfit, 
            W1.artistid as artistid
        FROM dtoohey.trans T1
        INNER JOIN dtoohey.WORK W1
        ON W1.workid = T1.workid
        GROUP BY artistid
    ) 
) TEMP1
INNER JOIN dtoohey.artist A1
    ON A1.artisid = TEMP1.artistid
where TEMP1.rnk = 1

This uses the RANK() function which will return more than one row if several artists achieve the same average profit. You might want to use ROW_NUMBER() instead. Analytic functions can be very powerful. Find out more.

You can apply ROWN_NUMBER(), RANK() and DENSE_RANK() to any top-n problem. You can use one of them to solve your first problem too.

---

"however the avg profit is null."

That's probably a data issue. If one of the numbers in (salesPrice - AcquisitionPrice) is null the result will be null, and won't be included in the average. If all the rows for an artist are null the AVG() will be null.

As it happens the sort order will put NULL last. But as the PARTITION BY clause sorts by AvgProfit desc that puts the NULL results at rank 1. The solution is to use the NULLS LAST in the windowing clause:

            , rank() over (order by avgProfit desc nulls last) as rnk

This will guarantee you a non-null result at the top (providing at least one of your artists has values in both columns).

回答2:

1st question - Oracle does not guarantee the order by which rows are retrieved. Hence you must first order and then limit the ordered set. SELECT * from ( SELECT A1.* From dtoohey.artist A1 INNER JOIN ( SELECT COUNT(W1.ArtistID) AS COUNTER, artistID FROM dtoohey.trans T1 INNER JOIN dtoohey.work W1 ON W1.workid = T1.Workid GROUP BY W1.artistID ) TEMP1 ON TEMP1.artistID = A1.artistID WHERE A1.artistID = TEMP1.artistId ORDER BY COUNTER desc ) WHERE ROWNUM = 1

2nd question: I believe (haven't tested) that you have that LIMIT 1 wrong. That keyword is for use with Bulk collecting.