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.
| Author |
Topic |
|
Yorkey181
Starting Member
3 Posts |
 Posted - 2012-04-16 : 21:38:33
|
| Hi, going quietly crazy here ... sure I've done this before but can't get it to work this time around...I've got a small table with a couple of hundred records ... I want to take 4 random records from the top 50 sorted by a points field.I though I used something like the following last time, but no go now ...SELECT Top 4 ID FROM myTable ORDER BY NewID() IN (SELECT Top 40 ID FROM myTable Order By Points DESCAny ideas much appreciated! ThanksMatt Brading |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2012-04-16 : 23:15:15
|
| Please try This.;WITH cte AS( SELECT *, ROW_NUMBER() OVER (ORDER BY NewID) AS RN FROM (SELECT TOP 40 ID, newid FROM #myTable Order by Points) t1)Select * from CTE where rn <=4 |
 |
|
|
Yorkey181
Starting Member
3 Posts |
Posted - 2012-04-17 : 00:31:29
|
| Thanks, but no go.I'm using newID() to get random records, so your (order by newid) throws an error. Starting to wonder if last time I just added the top 40 to a temp table and did a random call on that ... would work OK but seems to be the long way around! I'll keep lookin!Matt Brading |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-17 : 01:14:34
|
[code]SELECT Top 4 ID FROM ( SELECT Top 40 ID FROM myTable Order By Points DESC) as DORDER BY NewID()[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Yorkey181
Starting Member
3 Posts |
Posted - 2012-04-17 : 01:32:10
|
| Thank you! That's where I started but I missed the 'As D' bit ... of course it makes all the difference!Much appreciated.Matt Brading |
|
|
|
|
|
|
Query within Query