Error in ORDER BY NEWID() - 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)

Error in ORDER BY NEWID()

Author

Topic

mehr_83
Starting Member

6 Posts

Posted - 2009-05-21 : 05:16:38

when i use thius code:

SELECT     TOP (100) PERCENT id, per
FROM         dbo.ads
WHERE     (per = 100)
ORDER BY NEWID()
UNION ALL
SELECT     TOP (10) PERCENT id, per
FROM         dbo.ads
WHERE     (per = 50)
ORDER BY NEWID()

Error:

quote:
Unable to parse query text

please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 06:24:09

[code]SELECT TOP (100) PERCENT id, per
FROM dbo.ads
WHERE (per = 100)
~~ORDER BY NEWID()~~
UNION ALL
SELECT TOP (10) PERCENT id, per
FROM dbo.ads
WHERE (per = 50)
ORDER BY NEWID()[/code]

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

mehr_83
Starting Member

6 Posts

Posted - 2009-05-21 : 06:38:47

quote:

Originally posted by khtan

SELECT     TOP (100) PERCENT id, per
FROM         dbo.ads
WHERE     (per = 100)
ORDER BY NEWID()
UNION ALL
SELECT     TOP (10) PERCENT id, per
FROM         dbo.ads
WHERE     (per = 50)
ORDER BY NEWID()

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

this is not work

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 06:42:34

[code]
SELECT id, per
FROM dbo.ads
WHERE per in (50, 100)
ORDER BY NEWID()
[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 07:43:18

SELECT id, per
FROM dbo.ads
WHERE per in (50, 100)
ORDER BY per, NEWID()

E 12°55'05.63"
N 56°04'39.26"

mehr_83
Starting Member

6 Posts

Posted - 2009-05-21 : 07:48:45

quote:
Originally posted by Peso

SELECT id, per
FROM dbo.ads
WHERE per in (50, 100)
ORDER BY per, NEWID()

E 12°55'05.63"
N 56°04'39.26"

thnaks,
but
1. number of record is different
2. order of record is important
---

SELECT     TOP (100) id, per
FROM         dbo.ads
WHERE     (per = 100)
ORDER BY NEWID()
UNION ALL ("""then""")
SELECT     TOP (10) id, per
FROM         dbo.ads
WHERE     (per = 50)
ORDER BY NEWID()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 08:00:51

but your original post is TOP 100 PERCENT. not TOP 100 as in your last post.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 08:12:58

SELECT id, per
FROM dbo.ads
WHERE per in (50, 100)
ORDER BY per desc, NEWID()

E 12°55'05.63"
N 56°04'39.26"

mehr_83
Starting Member

6 Posts

Posted - 2009-05-21 : 08:51:39

quote:
Originally posted by Peso

SELECT id, per
FROM dbo.ads
WHERE per in (50, 100)
ORDER BY per desc, NEWID()

E 12°55'05.63"
N 56°04'39.26"

Iwould select 10 of record where per=100 AND select 20 of record where per=50

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 09:03:38

[code]
select *
from
(
select top 10 id, per
from dbo.ads
where per = 100
order by newid()
) a
union all
select *
from
(
select top 20 id, per
from dbo.ads
where per = 50
order by newid()
) b
[/code]

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

mehr_83
Starting Member

6 Posts

Posted - 2009-05-21 : 09:13:07

thanks a lot
this is work.
very very thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources