Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I randomize the DISTINCT TOP(4) returned SQL rows for this query?

Posted on 2009-05-07
2
Medium Priority
?
331 Views
Last Modified: 2012-05-06
Hi, I'm trying to develop an SQL query that returns 4 random rows.

Basically it looks at 4 entries found in eord_line and then returns all matching items from epar_rels. It will return around 12 results but II only want 4 distinct randomly selected ones.

Any ideas as I can't use DISTINCT with ORDER BY NEWID().

Thanks
SELECT     TOP (4) epar_rels.relative, plis_brks.price, epar.descr
FROM         epar_rels LEFT OUTER JOIN
                      eord_line ON epar_rels.part = eord_line.part LEFT OUTER JOIN
                      plis_brks ON epar_rels.relative = plis_brks.part LEFT OUTER JOIN
                      epar_stoc ON epar_rels.relative = epar_stoc.part LEFT OUTER JOIN
                      epar ON epar_rels.relative = epar.part
WHERE     (eord_line.ref_no = 'TEST000663') AND (epar_stoc.stock > 0) AND (plis_brks.price_list = 'Q42004WEB')
ORDER BY NEWID()

Open in new window

0
Comment
Question by:joshgeake
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 24323573
try this;:
SELECT TOP 4 *
FROM (
SELECT     DISTINCT epar_rels.relative, plis_brks.price, epar.descr
FROM         epar_rels LEFT OUTER JOIN
                      eord_line ON epar_rels.part = eord_line.part LEFT OUTER JOIN
                      plis_brks ON epar_rels.relative = plis_brks.part LEFT OUTER JOIN
                      epar_stoc ON epar_rels.relative = epar_stoc.part LEFT OUTER JOIN
                      epar ON epar_rels.relative = epar.part
WHERE     (eord_line.ref_no = 'TEST000663') AND (epar_stoc.stock > 0) AND (plis_brks.price_list = 'Q42004WEB')
) sq
ORDER BY NEWID()

Open in new window

0
 
LVL 2

Author Closing Comment

by:joshgeake
ID: 31578911
Super, thanks :D
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

972 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