the title more sold in Pubs database without using CURSOR

Question

Post reply

the title more sold in Pubs database without using CURSOR

luis martin ortiz barrutia

SSC Enthusiast

Points: 189
More actions
September 25, 2011 at 4:13 pm

#257907

I am practicing sql server 2008, i want to ask if there is any way to find the title more sold in the database Pubs without using cursor (obviosuly i hve to use tables titles and sales )
Thank you in adavance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

read up on inner joins, sum, group by, order by and top

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Read up on the subjects that Remi recommended above, try some code on your own, and if you still can't figure it out, post your code and we'll be happy to help. You'll learn a lot more than one of us just writing the code for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

luis martin ortiz barrutia SSC Enthusiast Points: 189 More actions · Answer 3

This is the way i solve this problem.

Anybody has a more efficient solution?

Here is the code

create procedure libromasvendido as
DECLARE @VARtitle_id varchar(6),@VARtitle varchar(80),@VARprice int
DECLARE @VARcantidad int, @VARtotalvendido int
/* SELECT @VARtotalvendido = SUM(qty) from sales
Print @VARtotalvendido*/
/* variable los mas vendidos mv*/
DECLARE @VARtitle_idmv varchar(6),
@VARtitlemv varchar(80), @VARtotalvendidomv int
SELECT @VARtitle_id = SPACE(6)
SELECT @VARtitle = SPACE(80)
SELECT @VARprice = 0
SELECT @VARcantidad = 0
SELECT @VARtotalvendido = 0
SELECT @VARtitle_idmv = SPACE(6)/*variable el mas vendido*/
SELECT @VARtitlemv = SPACE (80)/*variable el mas vendido*/
SELECT @VARtotalvendidomv = 0/*variable el mas vendido*/
/*se crea cursor*/
Declare lmlibros_cursor CURSOR FOR
SELECT titles.title_id,title,price from titles
order by titles.title_id
/**/
open lmlibros_cursor
/*FETCH NEXT FROM lmlibros_cursor into @VARtitle_id,@VARtitle,@VARprice,@VARcantidad*/
FETCH NEXT FROM lmlibros_cursor into @VARtitle_id, @VARtitle, @VARprice
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @VARtotalvendido = 0
SELECT @VARtotalvendido = SUM(qty*@VARprice) from sales where title_id= @VARtitle_id
PRINT 'Title Sold '+ @VARtitle
PRINT 'Total amount sold '+str(@VARtotalvendido)
IF @VARtotalvendidomv <= @VARtotalvendido
BEGIN
SELECT @VARtitlemv = @VARtitle
SELECT @VARtotalvendidomv = @VARtotalvendido
END
FETCH NEXT FROM lmlibros_cursor into @VARtitle_id, @VARtitle, @VARprice
END
PRINT 'The most sold title '+ @VARtitlemv
PRINT 'Total Amount sold'+str(@VARtotalvendidomv)
close lmlibros_cursor
RETURN

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

That solition can be found in a single query.

Use inner join to match the rows between the tables.

Sum to get the total sales

Group by to (you'll see)

order by DESC to sort the data

and then top n to get n rows

luis martin ortiz barrutia SSC Enthusiast Points: 189 More actions · Answer 5

The result you suggest is a set of records, I mean a single record

Thank you in advance

dogramone Hall of Fame Points: 3216 More actions · Answer 6

so then just add a top 1 and order by and you will get the single record from the results set