MS SQL random results question in a query

Posted on 2010-03-23
Medium Priority
Last Modified: 2012-05-09
I have a table with 2 colums:   Location    Word
that looks like this:
W1  Dust
W1  Dirt
W1  Soil
W1  Mud
W2  Rock
W2  Brick
W3  Fly
W3  Run
W3  Jump

Each location can have any number of words in it.  I have a total of 31 locations (W1 thru W31).

I need a select statement that will select 1 random word from each location  (i.e. returning 31 rows)

Can someone help me with this?  Thank you.

Question by:arthurh88
LVL 23

Accepted Solution

Rajkumar Gs earned 2000 total points
ID: 28373396
Check this

create table tblRandom
	Location varchar(20),
	Word	varchar(20)

insert into tblRandom
select 'W1',  'Dust'
union  all
select 'W1',  'Dirt'
union  all
select 'W1',  'Soil'
union  all
select 'W1',  'Mud'
union  all
select 'W2',  'Rock'
union  all
select 'W2',  'Brick'
union  all
select 'W3',  'Fly'
union  all
select 'W3',  'Run'
union  all
select 'W3',  'Jump'

select * from tblRandom

-- Random Query
select DISTINCT Location, 
	(SELECT TOP 1 Word 
    FROM tblRandom 
	WHERE Location = R.Location  
FROM tblRandom R

Open in new window


Author Closing Comment

ID: 31706225

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question