Please help with a select query

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

Transact-SQL (2000)

Please help with a select query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-19 : 18:48:14

JENNIFER LASSITER writes "I have a database that is coded with 6 different segments, like gc, gb, rb, rc, sb, sc. Each segment could have 1-1000 records. I need to know how to get 3 random records from each of the six different segments.

I am trying to create a data transformation package that will extract the 3 random records from each segment and write them to a new .dbf. I'm at a loss on how to achieve this.

I did a select query like:
select Top 3 * from TABLE_NAME
where segment = 'gc'
order by
newid()

This will work, but only for the gc segment. I tried using OR and adding all the segments and changing to TOP 18, but it would give me more than 3 or less than 3 for each segment.

Can you give me some advice?

Thanks,"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-19 : 19:41:00

insert into temp table for each segment


insert into #temp select Top 3 * from TABLE_NAME where segment = 'gc' order by newid()
insert into #temp select Top 3 * from TABLE_NAME where segment = 'gb' order by newid()
...

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-19 : 23:35:31

if u don't have a performance issue means try this otherwise follow khtan suggession

select Top 3 * from TABLE_NAME
where segment = 'gc'
union
select Top 3 * from TABLE_NAME
where segment = 'gb'
union
select Top 3 * from TABLE_NAME
where segment = 'rb'
union
select Top 3 * from TABLE_NAME
where segment = 'rc'
union
select Top 3 * from TABLE_NAME
where segment = 'sb'
union
select Top 3 * from TABLE_NAME
where segment = 'sc'
order by
newid()

-- KK

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-20 : 01:50:29

I think that should be:

SELECT *
FROM (
   select Top 3 * from TABLE_NAME
   where segment = 'gc'
   order by newid()) gc
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'gb'
   order by newid()) gb
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'rb'
   order by newid()) rb
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'rc'
   order by newid()) rc
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'sb'
   order by newid()) sb
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'sc'
   order by newid()) sc

and performance shouldn't be bad at all.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-20 : 02:06:57

ok PSamsig,

I have one doubt!

is the union or union all causes any performance issues in million of records ?

-- KK

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-20 : 02:24:52

In the above no.
I added the ALL just to explecitly imply that I wanted all rows from all subqueries, basically I dont know the underlying data, there could be duplicates and you stated 3 random rows, not 3 random unique rows. They will of course be unique across the segemnts with or without the ALL.
And its a habit, I once read that you should never write SELECT DISTINCT unless you need it (it saves a sorting) and like wise with UNION which has implisit distinctness unless you applie ALL.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-20 : 02:48:59

quote:

Originally posted by PSamsig

I think that should be:

SELECT *
FROM (
   select Top 3 * from TABLE_NAME
   where segment = 'gc'
   order by newid()) gc
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'gb'
   order by newid()) gb
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'rb'
   order by newid()) rb
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'rc'
   order by newid()) rc
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'sb'
   order by newid()) sb
union ALL (
   select Top 3 * from TABLE_NAME
   where segment = 'sc'
   order by newid()) sc

and performance shouldn't be bad at all.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.


You will get the syntax error. Try it with Northwind
use Northwind
SELECT *
FROM (
   select Top 3 * from Customer
   order by newid()
	) a
union ALL (
   select Top 3 * from Customer
   order by newid()) b

-- Incorrect syntax near the keyword 'order'.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-20 : 03:12:59

Yeah I was a bit to quick. That fixed and I ran into an other problem I hadn't forseen, the damn thing has restrictions on ORDER BY in UNIONs. Well I got this to work:

SELECT * FROM (
   select Top 3 newid() as newid, * from TABLE_NAME
   where segment = 'gc'
   order by 1) gc
union ALL SELECT * FROM (
   select Top 3 newid() as newid, * from TABLE_NAME
   where segment = 'gb'
   order by 1) gb
union ALL SELECT * FROM (
   select Top 3 newid() as newid, * from TABLE_NAME
   where segment = 'rb'
   order by 1) rb
union ALL SELECT * FROM (
   select Top 3 newid() as newid, * from TABLE_NAME
   where segment = 'rc'
   order by 1) rc
union ALL SELECT * FROM (
   select Top 3 newid() as newid, * from TABLE_NAME
   where segment = 'sb'
   order by 1) sb
union ALL SELECT * FROM (
   select Top 3 newid() as newid, * from TABLE_NAME
   where segment = 'sc'
   order by 1) sc

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources