select by random - 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 2005 Forums

Transact-SQL (2005)

select by random

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 07:09:19

hi
i have a table named images[id (int), pic (image)]
i want to write a query that return 3 record by random and also a record with id=1
results must be in order by id
it means that the first row have id=1 and 3 random records

****<>****

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-04 : 07:15:35

[code]
select top 1 id, pic from images where id = 1

union all

select id, pic
from (select top 3 id, pic from images where id <> 1 order by newid()) a
[/code]

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

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 07:32:27

error:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

****<>****

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 07:35:59

i tried this, it wroks,but i think there is a better way to solve the problem

drop table #i
create table #i(
id int
)
insert into #i
select id from images where id<>1 order by newid()
select * from images g inner join (
select 1 as id union
select * from #i
)s
on g.id=s.id
order by g.id

****<>****

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-04 : 08:01:48

quote:
Originally posted by mahdi87_gh

error:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

****<>****

edited my post

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

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 08:13:19

thanks khtan
i appreciate your answer in msdn forum

****<>****

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 08:56:57

hi again
the code works with my sql server (local on my computer)
but it does'nt work with the sql server on my web server
i think their sql server 2005 is not updated.
error:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

do you know how to change the query so it can work on the server?

****<>****

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-04 : 09:02:12

my edited query should work on SQL 2005. I think the SQL Server on the web server is still on SQL 2000

try


select top 1 id, pic from #images where id = 1

union all

select id, pic 
from  
(
	select top 3 id, pic, nid = newid() from #images where id <> 1 order by nid
) a

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

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 09:08:07

it works
thanks again very very much

no,its sql server 2005 but i think it's not updated

****<>****

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-04 : 09:13:33

i don't get that error on my SQL 2005 or even in the compatibility level 80. Only in SQL 2000

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-05 : 03:39:34

quote:
Originally posted by mahdi87_gh

hi
i have a table named images[id (int), pic (image)]
i want to write a query that return 3 record by random and also a record with id=1
results must be in order by id
it means that the first row have id=1 and 3 random records

****<>****

Try this too

select top 4 id from your_table
order by case when id=1 then 1 else 2 end,newid()

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-05 : 05:18:22

quote:
Originally posted by khtan

i don't get that error on my SQL 2005 or even in the compatibility level 80. Only in SQL 2000

So much for Compatibility mode then ...

Dunno why folk "trust" to that ... if you gotta do a regression test anyway might as well do it with "native" compatibility mode on a new version.

Sorry, didn't mean to hijack the thread </rant>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-05 : 06:40:59

quote:
Originally posted by Kristen

quote:
Originally posted by khtan

i don't get that error on my SQL 2005 or even in the compatibility level 80. Only in SQL 2000

So much for Compatibility mode then ... Dunno why folk "trust" to that ... if you gotta do a regression test anyway might as well do it with "native" compatibility mode on a new version.

Sorry, didn't mean to hijack the thread </rant>

I hope my solution will work in all versions properly

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources