CODEWITHSUNDEEP
sql-servert-sql
Le_RedditUser
Le_RedditUser

Reputation: 113

Subquery with Ties

Goal: Show the ID, title of the books and the pub. Year for the book with the highest sales amount; include ties. Use the total extended cost (quantity * order price) when determining the sales of a book.

Rules:

Query:

SELECT  book_id,
        title,
        year_publd
FROM bkinfo.books
where book_id =
    (
    SELECT TOP 1 WITH TIES book_id, quantity*order_price as Extended_cost
    from bkorders.order_details
    order by quantity*order_price DESC
    )
;

Error:

Msg 116, Level 16, State 1, Line 10 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

The subquery shows the book_id with the highest amount but when running it as a whole, I receive an error. Please explain what I should do.

Upvotes: 0

Views: 142

Answers (2)

Barck-obama
Barck-obama

Reputation: 21

;with cte as
(
select book_id, quantity*order_price as Extended_cost from bkorders.order_details
order by Extended_cost DESC
)

select top 1 with ties * from cte

Upvotes: 2

dean
dean

Reputation: 10098

SELECT book_id, title, year_publd
FROM bkinfo.books
where book_id =
    (
    SELECT TOP 1 book_id
    from bkorders.order_details
    order by quantity*order_price DESC

    )
;

Upvotes: 1

Related Questions