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 2008 Forums
 Transact-SQL (2008)
 get set of random rows with distinct values

Author  Topic 

jstranger
Starting Member

11 Posts

Posted - 2013-06-17 : 07:38:46
I have an interesting little T-SQL problem that has me beaten.

I want to retrieve a small set of randomly selected rows from a table but also ensure that a particular column (AUTHORID) is unique (not duplicated) within that set.

SELECT TOP 6 WORKID, AUTHORID FROM dbo.LITERARYWORKS
ORDER BY CHECKSUM(NEWID())

works fine at getting a random selection, but of course does not prevent duplicates on AUTHORID. And I can't use DISTINCT without including WORKID and AUTHORID in the ORDER BY - and in any case the rows would always be unique if WORKID is included. I believe one way of achieving the effect of a 'DISTINCT' on a single column while returning other columns as well is by using GROUP BY, but I can't see how to use that in combination with ORDER BY.

Any ideas?



Jon Stranger

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 08:01:30
--Is it ok?
SELECT TOP 6 MAX(WORKID) WORKID, AUTHORID
FROM dbo.LITERARYWORKS
GROUP BY AUTHORID
ORDER BY CHECKSUM(NEWID())

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 09:17:34
[code]
SELECT TOP 6 WORKID,AUTHORID
FROM
(
SELECT WORKID, AUTHORID,ROW_NUMBER() OVER (PARTITION BY AUTHORID ORDER BY WORKID DESC) AS Seq
FROM dbo.LITERARYWORKS
)t
WHERE Seq=1
ORDER BY CHECKSUM(NEWID())
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 23:55:38
quote:
Originally posted by visakh16


SELECT TOP 6 WORKID,AUTHORID
FROM
(
SELECT WORKID, AUTHORID,ROW_NUMBER() OVER (PARTITION BY AUTHORID ORDER BY WORKID DESC) AS Seq
FROM dbo.LITERARYWORKS
)t
WHERE Seq=1
ORDER BY CHECKSUM(NEWID())


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Hi visakh,
My solution is also same as yours....
which one is optimized code?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 00:24:01
quote:
Originally posted by bandi

quote:
Originally posted by visakh16


SELECT TOP 6 WORKID,AUTHORID
FROM
(
SELECT WORKID, AUTHORID,ROW_NUMBER() OVER (PARTITION BY AUTHORID ORDER BY WORKID DESC) AS Seq
FROM dbo.LITERARYWORKS
)t
WHERE Seq=1
ORDER BY CHECKSUM(NEWID())


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Hi visakh,
My solution is also same as yours....
which one is optimized code?

--
Chandu


Test it out yourself
I dont have a sql box to text this here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-18 : 00:32:40
In your query there are additional 3 operations (Filter, Sequence Project and segment)....
My query had "Stream Aggregate" instead of the above 3 operations...

Total cost is same for both queries ( with my sample data).. But it may differ based on original data...

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 00:51:07
quote:
Originally posted by bandi

In your query there are additional 3 operations (Filter, Sequence Project and segment)....
My query had "Stream Aggregate" instead of the above 3 operations...

Total cost is same for both queries ( with my sample data).. But it may differ based on original data...

--
Chandu


Ok...thanks for the info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-18 : 00:53:42
quote:
Originally posted by visakh16

quote:
Originally posted by bandi

In your query there are additional 3 operations (Filter, Sequence Project and segment)....
My query had "Stream Aggregate" instead of the above 3 operations...

Total cost is same for both queries ( with my sample data).. But it may differ based on original data...
--
Chandu


Ok...thanks for the info




--
Chandu
Go to Top of Page
   

- Advertisement -