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.LITERARYWORKSORDER 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.LITERARYWORKSGROUP BY AUTHORID ORDER BY CHECKSUM(NEWID())--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 09:17:34
|
[code]SELECT TOP 6 WORKID,AUTHORIDFROM(SELECT WORKID, AUTHORID,ROW_NUMBER() OVER (PARTITION BY AUTHORID ORDER BY WORKID DESC) AS Seq FROM dbo.LITERARYWORKS)tWHERE Seq=1ORDER BY CHECKSUM(NEWID())[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-17 : 23:55:38
|
quote: Originally posted by visakh16
SELECT TOP 6 WORKID,AUTHORIDFROM(SELECT WORKID, AUTHORID,ROW_NUMBER() OVER (PARTITION BY AUTHORID ORDER BY WORKID DESC) AS Seq FROM dbo.LITERARYWORKS)tWHERE Seq=1ORDER BY CHECKSUM(NEWID()) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Hi visakh,My solution is also same as yours.... which one is optimized code?--Chandu |
|
|
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,AUTHORIDFROM(SELECT WORKID, AUTHORID,ROW_NUMBER() OVER (PARTITION BY AUTHORID ORDER BY WORKID DESC) AS Seq FROM dbo.LITERARYWORKS)tWHERE Seq=1ORDER BY CHECKSUM(NEWID()) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
|