How can I acomplish The Following

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 2005 Forums

Transact-SQL (2005)

How can I acomplish The Following

Author

Topic

hjavaher
Starting Member

16 Posts

Posted - 2009-10-04 : 23:03:52

Hi, I need to write a Select statement that grabs only 2 unique Usernames in a random order. I have the following but it keeps giving me errors and won't even run.

Any help would be greatly appreciated,
Thanks,
Jason

Current Select statement:

SELECT TOP (@Count) Distinct(UserProfile.UserName) , MediaList.MediaTitle
FROM [MediaList]
WHERE (UserProfile.Gender = @Gender)
ORDER BY NEWID()

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-05 : 01:35:47

Just try like this

select top 2 UserName from MediaList
order by newid()

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-05 : 01:40:03

what is the MediaList.MediaTitle for ? Do you need the column ?


select top 2 UserName
from   UserProfile
where  Gender = @Gender
order by newid()

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-05 : 01:46:57

INNER JOIN the previous query to MediaList, if you need the corresponding MediaTitle from the MediaList table


select *
from
(
select top 2 UserName
from   UserProfile
where  Gender = @Gender
order by newid()
) u
inner join MediaList m on u.UserName = m.UserName

KH
[spoiler]Time is always against us[/spoiler]

hjavaher
Starting Member

16 Posts

Posted - 2009-10-05 : 02:01:00

I do need the MediaList.MediaTitle and I need the Usernames to be unique. This table contains a list of all videos submitted by users. I need to pick two videos out of this table that different users has submitted. so The two videos can not be owned by the same person.

thanks for the quick responses.
Jason

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-05 : 02:39:36

quote:
Originally posted by hjavaher

I do need the MediaList.MediaTitle and I need the Usernames to be unique. This table contains a list of all videos submitted by users. I need to pick two videos out of this table that different users has submitted. so The two videos can not be owned by the same person.

thanks for the quick responses.
Jason

This will help you!

select top 2 UserName,MediaTitle from
(select distinct(UserName),MediaTitle from MediaList where Gender = @Gender) as a
order by newid()

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-05 : 03:10:14

try


select top 2 *
from
(
     select u.UserName, m.MediaTitle, row_no = row_number() partition by (u.UserName order by newid())
     from   UserProfile u inner join MediaList m on u.UserName = m.UserName
     where  u.Gender = @Gender
) d
where d.row_no = 1
order by newid()

KH
[spoiler]Time is always against us[/spoiler]

hjavaher
Starting Member

16 Posts

Posted - 2009-10-06 : 14:10:29

Thank you, That is excellent. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources