Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Randomly picked rows from union all results

6 posts in General Discussion Last posting was on 2013-02-27 15:13:04.0Z
blue Posted on 2013-02-25 14:01:33.0Z
Sender: 2858.512b6d22.1804289383@sybase.com
From: Blue
Newsgroups: sybase.public.iq
Subject: Randomly picked rows from union all results
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <512b6ebd.290e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Feb 2013 06:01:33 -0800
X-Trace: forums-1-dub 1361800893 172.20.134.41 (25 Feb 2013 06:01:33 -0800)
X-Original-Trace: 25 Feb 2013 06:01:33 -0800, 172.20.134.41
Lines: 5
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4603
Article PK: 1307902

Hi
please help me to get randomly picked rows example 1000 from
result of 2 queries joined with UNION ALL.

Thank you advanced!


Abhijit Jadhav Posted on 2013-02-25 15:49:26.0Z
Sender: 32c0.512b86f7.1804289383@sybase.com
From: Abhijit Jadhav
Newsgroups: sybase.public.iq
Subject: Re: Randomly picked rows from union all results
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <512b8806.332a.1681692777@sybase.com>
References: <512b6ebd.290e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Feb 2013 07:49:26 -0800
X-Trace: forums-1-dub 1361807366 172.20.134.41 (25 Feb 2013 07:49:26 -0800)
X-Original-Trace: 25 Feb 2013 07:49:26 -0800, 172.20.134.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4604
Article PK: 1307907

You may try set rowcount 1000 before select. As the
resultset in IQ are not in any order.

NOTE: set rowcount does not work for DELETE statement like
how it works in ASE. In IQ it is only applicable to select
queries.

Regards,
Abhijit

> Hi
> please help me to get randomly picked rows example 1000
> from result of 2 queries joined with UNION ALL.
>
> Thank you advanced!


Blue2509 Posted on 2013-02-26 07:05:15.0Z
Sender: 486.512c5ccb.1804289383@sybase.com
From: Blue2509
Newsgroups: sybase.public.iq
Subject: Re: Randomly picked rows from union all results
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <512c5eab.521.1681692777@sybase.com>
References: <512b8806.332a.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Feb 2013 23:05:15 -0800
X-Trace: forums-1-dub 1361862315 172.20.134.41 (25 Feb 2013 23:05:15 -0800)
X-Original-Trace: 25 Feb 2013 23:05:15 -0800, 172.20.134.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4605
Article PK: 1307925

Thank you for answer. I tried as follows:

SET ROWCOUNT 1000;
select xxxx

but it takse first 1000 rows not random. So I need better
solution.

Best wishes
Thea

> You may try set rowcount 1000 before select. As the
> resultset in IQ are not in any order.
>
> NOTE: set rowcount does not work for DELETE statement like
> how it works in ASE. In IQ it is only applicable to select
> queries.
>
> Regards,
> Abhijit
> > Hi
> > please help me to get randomly picked rows example 1000
> > from result of 2 queries joined with UNION ALL.
> >
> > Thank you advanced!


blue Posted on 2013-02-26 07:05:54.0Z
Sender: 486.512c5ccb.1804289383@sybase.com
From: Blue
Newsgroups: sybase.public.iq
Subject: Re: Randomly picked rows from union all results
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <512c5ed2.52c.1681692777@sybase.com>
References: <512c5eab.521.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Feb 2013 23:05:54 -0800
X-Trace: forums-1-dub 1361862354 172.20.134.41 (25 Feb 2013 23:05:54 -0800)
X-Original-Trace: 25 Feb 2013 23:05:54 -0800, 172.20.134.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4606
Article PK: 1307926


> Thank you for answer. I tried as follows:
>
> SET ROWCOUNT 1000;
> select xxxx
>
> but it takse first 1000 rows not random. So I need better
> solution.
>
> Best wishes
>
>
>
> > You may try set rowcount 1000 before select. As the
> > resultset in IQ are not in any order.
> >
> > NOTE: set rowcount does not work for DELETE statement
> > like how it works in ASE. In IQ it is only applicable to
> > select queries.
> >
> > Regards,
> > Abhijit
> > > Hi
> > > please help me to get randomly picked rows example
> > > 1000 from result of 2 queries joined with UNION ALL.
> > >
> > > Thank you advanced!


Abhijit Jadhav Posted on 2013-02-27 13:55:55.0Z
Sender: 2fd4.512e0f10.1804289383@sybase.com
From: Abhijit Jadhav
Newsgroups: sybase.public.iq
Subject: Re: Randomly picked rows from union all results
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <512e106b.3045.1681692777@sybase.com>
References: <512c5ed2.52c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Feb 2013 05:55:55 -0800
X-Trace: forums-1-dub 1361973355 172.20.134.41 (27 Feb 2013 05:55:55 -0800)
X-Original-Trace: 27 Feb 2013 05:55:55 -0800, 172.20.134.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4607
Article PK: 1307976

You may try....or play around the logic

declare @random float
select @random = rand()
select * from tablename where rand(ROWID(tablename)) <=
@random

Regards,
Abhijit

> > Thank you for answer. I tried as follows:
> >
> > SET ROWCOUNT 1000;
> > select xxxx
> >
> > but it takse first 1000 rows not random. So I need
> > better solution.
> >
> > Best wishes
> >
> >
> >
> > > You may try set rowcount 1000 before select. As the
> > > resultset in IQ are not in any order.
> > >
> > > NOTE: set rowcount does not work for DELETE statement
> > > like how it works in ASE. In IQ it is only applicable
> > > to select queries.
> > >
> > > Regards,
> > > Abhijit
> > > > Hi
> > > > please help me to get randomly picked rows example
> > > > 1000 from result of 2 queries joined with UNION ALL.
> > > >
> > > > Thank you advanced!


máca Posted on 2013-02-27 15:13:04.0Z
Sender: 3583.512e2028.1804289383@sybase.com
From: máca
Newsgroups: sybase.public.iq
Subject: Re: Randomly picked rows from union all results
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <512e2280.3671.1681692777@sybase.com>
References: <512c5eab.521.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Feb 2013 07:13:04 -0800
X-Trace: forums-1-dub 1361977984 172.20.134.41 (27 Feb 2013 07:13:04 -0800)
X-Original-Trace: 27 Feb 2013 07:13:04 -0800, 172.20.134.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4608
Article PK: 1307977

It depend on your random definition :-)

IMHO if SELECT * FROM ... is not ordered, then result depend
on server internal logic and it can vary from version to
version (RANDOM = UNORDERED). If you mean random as SELECT
where rowid is ordered by pseudo randomized number, use
previous advice.

> Thank you for answer. I tried as follows:
>
> SET ROWCOUNT 1000;
> select xxxx
>
> but it takse first 1000 rows not random. So I need better
> solution.
>
> Best wishes
> Thea
>
>
>
> > You may try set rowcount 1000 before select. As the
> > resultset in IQ are not in any order.
> >
> > NOTE: set rowcount does not work for DELETE statement
> > like how it works in ASE. In IQ it is only applicable to
> > select queries.
> >
> > Regards,
> > Abhijit
> > > Hi
> > > please help me to get randomly picked rows example
> > > 1000 from result of 2 queries joined with UNION ALL.
> > >
> > > Thank you advanced!