Random Select Query
-
Hello, I need some help with a select query.
I have a db with jokes and I want to display random 5 jokes per page based on joke category (JokeCatID). What I need is a query that will select only one joke from the same user per page.
Table:
JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID,
I was trying
SELECT DISTINCT JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
FROM jokes
WHERE JokeCatID = SomeNumber
ORDER BY NEWID()
which gave me random jokes however the DISTINCT did not work and more that one joke from same user was returned.
Thanks J.
-
Try this..
SELECT JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
from (
SELECT DISTINCT top 5 JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID,
newid()
FROM jokes
WHERE JokeCatID = SomeNumber
ORDER BY NEWID()
) a
order by JokeID
MohammedU
Microsoft SQL Server MVP -
I did try but it didnt work.
I got an error "No column was specified for column 6 of 'a'."
Thanks for your time
J.
-
That would correct the error, but I can't test the query to make sure it works :
SELECT JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
from (
SELECT DISTINCT top 5 JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserIDFROM jokes
WHERE JokeCatID = SomeNumber
ORDER BY NEWID()
) a
order by JokeID
-
Thanks to all for the help. I was able to make it work based on the help I got here. I just made some small changes.
Thanks again for the help.
Here is the code I'm using
SELECT JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
from (
SELECT DISTINCT top 5 JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
FROM jokes
WHERE JokeCatID = SomeNumber
) a
ORDER BY NEWID()
-
That won't work. You will always get the same 5 jokes for each category. Their final presentation order will change each time but that's it. If you need to change the order and the joke, the order by must be in the inner query.
-
I believe you should use "ORDER BY NEWID() " in inner query to get the unique rows ...not the outer query to get the unique rows...
MohammedU
Microsoft SQL Server MVP -
OK,
You are both somewhat right, I celebrated too early because I got no error when I used that query. The problem is that even if I use the ORDER BY NEWID() in the inner query I will not get unique results. It will give me 5 Jokes but some of them will be from a same user and some of them will even be displayed multiple times.
Ninjas SQL
-----------------
SELECT JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
from (
SELECT DISTINCT top 5 JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserIDFROM jokes
WHERE JokeCatID = SomeNumber
ORDER BY NEWID()
) a
order by JokeID
It will return this error
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
WHen I tried this
-----------------------
SELECT JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
from (
SELECT DISTINCT top 5 JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID,
NEWID() AS RandomID
FROM jokes
WHERE JokeCatID = SomeNumber
ORDER BY RandomID
) a
order by JokeID
I got no errors by how I said some of the results have same UserID and sometimes some jokes are displayed multiple times
Thanks for all the help
J.
-
Can anyone help? We have so much talent here and we cannot figure out one random select? All I need is to get 5 random jokes from a different author. It could be tested on any DB or any table. Thank you!
Jan
-
Ya this one was far from simple.
Please post back if you have any questions :
--BASE TABLEDECLARE @Jokes TABLE (UserID INT, CatID INT, JokeID INT)
INSERT INTO @Jokes (UserID, CatID, JokeID)
SELECT 1, 1, 1
UNION ALL
SELECT 2, 1, 2
UNION ALL
SELECT 3, 1, 3
UNION ALL
SELECT 4, 1, 4
UNION ALL
SELECT 4, 1, 5
UNION ALL
SELECT 5, 1, 6
UNION ALL
SELECT 2, 2, 7
UNION ALL
SELECT 3, 2, 8
UNION ALL
SELECT 4, 2, 9
UNION ALL
SELECT 5, 2, 10
UNION ALL
SELECT 1, 1, 11
UNION ALL
SELECT 1, 1, 12
UNION ALL
SELECT 3, 1, 13
UNION ALL
SELECT 2, 1, 14
UNION ALL
SELECT 4, 1, 15
UNION ALL
SELECT 5, 1, 16
DECLARE @CategoryID INT -- SHOULD BE A PARAMETER
SET @CategoryID = 1
--SET @CategoryID = 2
DECLARE @JokesWanted INT -- COULD BE A PARAMETER
SET @JokesWanted = 5
DECLARE @TotalJokes INT -- LOCAL
DECLARE @ROWCOUNT INT --LOCAL
DECLARE @JokesCats TABLE (JokeID INT, UserID INT)
INSERT INTO @JokesCats (JokeID, UserID)
SELECT JokeID, UserID FROM @Jokes WHERE CatID = @CategoryID
DECLARE @Results TABLE (JokeID INT, UserID INT)
IF @@ROWCOUNT <= @JokesWanted
BEGIN
PRINT 'LESS THAN 6 JOKES IN THE CATEGORY'
INSERT INTO @Results (JokeID, UserID)
SELECT JokeID, UserID FROM @JokesCats
END
ELSE
BEGIN
PRINT '6 + JOKES IN THE CATEGORY'
SET @TotalJokes = 0
SET @ROWCOUNT = 1
WHILE @TotalJokes < @JokesWanted AND @ROWCOUNT > 0
BEGIN
INSERT INTO @Results (JokeID, UserID)
SELECT TOP 1 JokeID, UserID
FROM @JokesCats JC
WHERE NOT EXISTS (SELECT * FROM @Results R WHERE R.UserID = JC.UserID)
ORDER BY NEWID()
SET @ROWCOUNT = @@ROWCOUNT
IF @ROWCOUNT > 0
BEGIN
SET @TotalJokes = @TotalJokes + 1
END
END
--we know we have enough jokes to display, but maybe not by enough users
IF @TotalJokes < @JokesWanted
BEGIN
--not enough distinct users
--fill in the blanks
DECLARE @MissingROWS INT
SET @MissingROWS = @JokesWanted - @TotalJokes
--Can't do a math operation in this line
PRINT @MissingROWS
SET ROWCOUNT @MissingROWS
INSERT INTO @Results (JokeID, UserID)
SELECT JokeID, UserID
FROM @JokesCats JC
WHERE NOT EXISTS (SELECT * FROM @Results R WHERE R.JokeID = JC.JokeID)
ORDER BY NEWID()
SET ROWCOUNT 0
END
END
SELECT J.* FROM @Jokes J INNER JOIN @Results R ON J.JokeID = R.JokeID ORDER BY NEWID()
-
The problem is DISTINCT is going to be across the entire output so more than one joke from the same user can be output because they are different jokes and thus distinct.
Sounds like you want one joke per UserID (which I assume is your author) and you want this to be random. Plus it sounds like you want to randomly show only 5,
This should do the trick.
SELECT
TOP 5
JokeID,
JokeCatID,
JokeTitle,
JokeDesc,
UserID
FROM
dbo.Jokes C
INNER JOIN
(SELECT
--UserID, -- Uncomment if JokeID is not a unique key value
(SELECT TOP 1 JokeID FROM dbo.Jokes B WHERE A.UserID = B.UserID ORDER BY NEWID()) JokeID
FROM
(SELECT DISTINCT UserID FROM dbo.Jokes) A) D
ON
C.JokeID = D.JokeID
-- AND C.UserID = D.UserID -- Uncomment if JokeID is not a unique key value
ORDER BY
NEWID()
Basically what I am doing is for each Author find one JokeID at random. The I take all of these jokes and randomly select 5 of them to display.
Also keep in mind depending on how many Author's you have and how many jokes they have entered those with fewer jokes have more of a likelyhood of the same joke appearing that those with more. And if oyu have only 5 Authors 1 of which has only 1 joke then the query would always return that one joke. Just want you to be aware of that potnetial.
-
Thank GOD (Antares)!.
I knew there was a better way of doing this.
Now I'll be able to sleep tonight
.
-
Glad to help. Also just realized, I am sure you have a Users table of some kind which means you can change this line to
(SELECT DISTINCT UserID FROM dbo.Jokes) A) D
to something like this
dbo.Users A) D
since you need just the distinct list of Authors that would be more efficient than the DISTINCT scenario is.
-
Yup and it is missing the categoryID part, but I'm sure he can finish up the work now
. -
Or you can use this sample from my previous aherm "attemp"
.--BASE TABLE
DECLARE @Jokes TABLE (UserID INT, CatID INT, JokeID INT)
INSERT INTO @Jokes (UserID, CatID, JokeID)
SELECT 1, 1, 1
UNION ALL
SELECT 2, 1, 2
UNION ALL
SELECT 3, 1, 3
UNION ALL
SELECT 4, 1, 4
UNION ALL
SELECT 4, 1, 5
UNION ALL
SELECT 5, 1, 6
UNION ALL
SELECT 2, 2, 7
UNION ALL
SELECT 3, 2, 8
UNION ALL
SELECT 4, 2, 9
UNION ALL
SELECT 5, 2, 10
UNION ALL
SELECT 1, 1, 11
UNION ALL
SELECT 1, 1, 12
UNION ALL
SELECT 3, 1, 13
UNION ALL
SELECT 2, 1, 14
UNION ALL
SELECT 4, 1, 15
UNION ALL
SELECT 5, 1, 16
DECLARE @CategoryID INT -- SHOULD BE A PARAMETER
SET @CategoryID = 1
SELECT Final.* FROM @Jokes Final INNER JOIN
(
SELECT
(SELECT TOP 1 JokeID FROM @Jokes B WHERE B.UserID = dtUsers.UserID AND B.CatID = @CategoryID ORDER BY NEWID()) AS JokeID
FROM
(SELECT DISTINCT USERID FROM @Jokes J WHERE J.CatID = @CategoryID) dtUsers
) dtJokeIDS
ON Final.JokeID = dtJokeIDS.JokeID
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply