Select DISTINCT and Order BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select DISTINCT and Order BY

Author

Topic

roki13
Starting Member

2 Posts

Posted - 2007-03-27 : 15:11:50

Hi all,

I'm looking to select from a table some rows, but randomly and using a frequency value(because some of my rows must be selected more often than others).

So my piece of code starts like this:


SELECT @MaxFreq = MAX(Frequency) FROM Queries
WHILE @i <= @MaxFreq
	BEGIN
		INSERT INTO @ActiveQueries(ID, Word, Frequency, [Level], RandomNumber)
		SELECT ID, Word, Frequency, [Level], 1 + @SumFrequency*RAND(CHECKSUM(NEWID()))  FROM Queries
		WHERE Frequency >= @i
	
		SET @i = @i + 1
	END

this will insert all my table rows into @ActiveQueries and duplicate them by their respective "Frequency" field value.

Now, from @ActiveQueries, I would like to insert into a new table called @Results @x rows that I would have ordered by my RandomNumber field AND I want them unique.

I have started with something like this:


SET ROWCOUNT @x
INSERT INTO @Results (ID, Word, Frequency, [Level])
SELECT ID, Word, Frequency, [Level] 
FROM @ActiveQueries
ORDER BY RandomNumber

The only thing this query is missing would be a distinct on the select statement but then the ORDER BY would not work.

ANy idea how I should do that ? I'm about to give up and move the logic to .Net but I'm sure there is a solution...I hope my post is understanble, please let me know if it needs more details.

Thanks for your help,

Seb

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-27 : 18:26:43

I think this will work. The key is the TOP 100 PERCENT.

SET ROWCOUNT @x
INSERT INTO @Results (ID, Word, Frequency, [Level])
SELECT DISTINCT ID, Word, Frequency, [Level] 
FROM
(
	SELECT TOP 100 PERCENT ID, Word, Frequency, [Level] 
	FROM @ActiveQueries
	ORDER BY RandomNumber
) t

-Ryan

roki13
Starting Member

2 Posts

Posted - 2007-03-28 : 04:55:48

thanks ryan for helping.

I tried your solution but what is happening actually is that the DISTINCT sorts the results as well !
Example:


SET ROWCOUNT @x
INSERT INTO @Results (ID, Word, Frequency, [Level])
SELECT DISTINCT ID, Word, Frequency, [Level] 
FROM
(
	SELECT TOP 100 PERCENT ID, Word, Frequency, [Level] 
	FROM @ActiveQueries
	ORDER BY RandomNumber
) t

I will get always the same rows in @Results, that would be the first @x ones sorted by ID

If I do this :


SET ROWCOUNT @x
INSERT INTO @Results (Word, ID, Frequency, [Level])
SELECT DISTINCT Word, ID, Frequency, [Level] 
FROM
(
	SELECT TOP 100 PERCENT ID, Word, Frequency, [Level] 
	FROM @ActiveQueries
	ORDER BY RandomNumber
) t

I will get always the same rows, by Word this time. So the ORDER BY RandomNumber in the subquery is useless.

My thought was then to forget about the DISTINCT, and instead INSERT then DELETE Duplicates rows recursively until I get my @x rows. But that's getting to complicated and just non-sense from a performance prospective so I'm gonna put my logic in my .Net code I think....

Thanks anyway
Seb

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-28 : 13:39:45

I must be missing something, but it appears that you can just do you insert into @ActiveQueries without calculating the RandomNumber. Then you can delete any duplicates out of the table and assing the RandomNumber. Then you can just select your TOP XX from that table...?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-28 : 14:27:27

I was goofing around with something else and though this might work for you as well. I made the ReandomNumber an int for sample purposes so it wouldn't change between runs (I was using a GUID).

But, here is something that might work for you:

-- SET UP
DECLARE @ActiveQueries TABLE (ID INT, Word VARCHAR(10), Frequency INT, RandomNumber INT)
DECLARE @RandomQueries TABLE (ID INT, Word VARCHAR(10), Frequency INT, RandomNumber INT, PRIMARY KEY CLUSTERED(ID) WITH (IGNORE_DUP_KEY = ON))

INSERT @ActiveQueries
SELECT 1, 'a', 54, 5 UNION ALL
SELECT 3, 'c', 34, 8 UNION ALL
SELECT 1, 'k', 1, 89 UNION ALL
SELECT 8, 'z', 98, 34 UNION ALL
SELECT 2, 'q', 732, 21 UNION ALL
SELECT 9, 'l', 12, 18 UNION ALL
SELECT 2, 'n', 87, 200

-- Load Random Table
INSERT @RandomQueries
SELECT ID, Word, Frequency, RandomNumber
FROM @ActiveQueries
ORDER BY RandomNumber ASC

-- Get TOP X
SELECT TOP 5 *
FROM @RandomQueries
ORDER BY RandomNumber ASC

I think you have more than just an ID as a DISTINCT row, so you could just expand the PRIMARY KEY of the @RandomQueries table to include them all.

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources