CODEWITHSUNDEEP
sqlsql-servervariables
MrPickles7
MrPickles7

Reputation: 692

How Set Local Variable to Specific Value in Result Set?

I have this code working: 

SELECT TOP 1 COUNT(*) AS NumSold, Game.Title
FROM Game JOIN Purchase ON Game.GameID = Purchase.GameID
GROUP BY Game.Title
ORDER BY NumSold DESC ;

I want to save the GameID of the top row to a variable.

I have tried this, but can't seem to get the Game ID of the 'game' with the most 'purchase' rows. I can get the purchase row count of various games when I switch around the variables out of desperation.

SET @BestSellerID = (SELECT TOP 1 COUNT(Game.GameID) AS Best
                     FROM Game JOIN Purchase ON Game.GameID = Purchase.GameID
                     ORDER BY Best DESC
                     );

I have tried SELECT way too (INCORRECT SYNTAX NEAR TOP):

DECLARE @BestSellerID int;

SELECT @BestSellerID = TOP 1 COUNT(Game.GameID) AS NumSold, Game.Title
FROM Game JOIN Purchase ON Game.GameID = Purchase.GameID
GROUP BY Game.Title
ORDER BY NumSold DESC ;

Is this not possible because there's no way to get scalar result? Can someone please explain to me how get a variable from the result set? Is there some concept I'm missing, is there a special way to 'talk to' queries after they have been er queried? Thank you.

OK with user tshoemake's help and fumbling with syntax this fixed my problem:

    DECLARE @BestSellerID int;

with cte_bs as 
(
SELECT TOP 1 COUNT(Game.GameID) AS NumSold, Game.GameID
FROM Game JOIN Purchase ON Game.GameID = Purchase.GameID
GROUP BY Game.GameID
ORDER BY NumSold DESC
)
select @BestSellerID = (SELECT cte_bs.GameID FROM cte_bs)
select @BestSellerID AS BestSellerGameID;

Upvotes: 0

Views: 556

Answers (2)

Vijay Manohar
Vijay Manohar

Reputation: 493

You can use a ResultQuery inorder to store it to a local variable in java with Mysql database

My code suggestions is :

 ResultSet rs=st.executeQuery("SELECT TOP 1 COUNT(*) AS NumSold, Game.Title,Game.GameID
    FROM Game JOIN Purchase ON Game.GameID = Purchase.GameID
    GROUP BY Game.Title
    ORDER BY NumSold DESC ;");
    if(rs.next())
    int GameID=rs.getInt("Game.GameID");

//You can use a array if you want to store large set of numbers

Upvotes: 0

tshoemake
tshoemake

Reputation: 1351

I see you're selecting two element (GameID and Title). I imagine it's looking for only one GameID to store in the local variable. This is a total shot in the dark without sample data, but try this or use this to get to where you need to be.

DECLARE @BestSellerID int;

with cte as 
(
SELECT TOP 1 COUNT(Game.GameID) AS NumSold, Game.GameID
FROM Game JOIN Purchase ON Game.GameID = Purchase.GameID
GROUP BY Game.GameID
ORDER BY NumSold DESC
)
set @BestSellerID = select top 1 cte.GameID from cte 

select @BestSellerID

Upvotes: 1

Related Questions