Diaplay record rendomly

Question

0.00/5 (No votes)

See more:

Dear Sir

I have a following table in SQL Server. I want to write a query in such a way that each time i run the query it will display one record from each 'Type' randomly. I mean when i run the query it should display one record from 'fruit' type and one record from 'cold drinks' type. But every time it should display records randomly.

product
--------

id type name
--- ----- ------
1 fruits orange
2 cold drinks Coca-cola
3 cold drinks mirinda
4 fruits banana
5 fruits apple
6 fruits grapes

kindly help me Sir.

Sashibhusan Meher

Posted 12-Dec-11 1:10am

Sashibhusan Meher

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

koolprasad2003 · Answer 1 · 2011-12-12T01:14:00

Solution 1

Make two columns of fruit and colddrinks separately and fire following Query to get random values.

SQL

SELECT TOP 1 fruit, colddrinks FROM product ORDER BY NEWID()

Posted 12-Dec-11 1:14am

koolprasad2003

Comments

RaviRanjanKr 12-Dec-11 7:32am

My 5+

Sashibhusan Meher 12-Dec-11 7:38am

Sorry sir, it will not work. Because i want to display all the field from the table. And i cann't create separate column for all the Product Types. So please give me another solution if u can..

thank you

Amir Mahfoozi · Answer 2 · 2011-12-12T18:50:00

Here it is :

SQL

IF OBJECT_ID(N'tempdb..#pr') IS NOT NULL
  drop table #pr
  
select  ID, [type] , name , abs(CHECKSUM(NewId()))   rnd   into #pr from   product

;with a(t , mr) as
(
select type, MAX(rnd) rnd from #pr group by type
)
select p.id, p.type, p.name from a inner join #pr p on a.t = p.type and a.mr = p.rnd

It was impossible to avoid temporary tables because the random field was regenerated in joins and ruined all of my plans so I went for a temporary table.

Good Luck

Eduard Lu · Answer 3 · 2011-12-12T17:30:00

Solution 2

If you'd like to display a random record, try this up!

SQL

SELECT TOP 1 * FROM product
ORDER BY NEWID()

That should do it.

Regards,
Eduard

Posted 12-Dec-11 17:30pm

Eduard Lu

Comments

[no name] 12-Dec-11 23:31pm

oops didn't see, it's almost the same with solution1.

Sashibhusan Meher 13-Dec-11 1:55am

thank you sir.. But i also want that the query should retrieve one record from each type randomly. For Example if the table has two product type query should show two records in a single execution.