Solved

Trying to combine 2 Select statetments

Posted on 2009-04-13
3
175 Views
Last Modified: 2012-05-06
SELECT     TOP (1) F1
FROM         WordList
WHERE     (F1 > '')
ORDER BY NEWID()


SELECT     TOP (1) F2
FROM         WordList
WHERE     (F2 > '')
ORDER BY NEWID()

I want to take both of those select statements and combine them so that the results are two colums with one row.  
0
Comment
Question by:arthurh88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 39

Assisted Solution

by:appari
appari earned 150 total points
ID: 24134238
with A as (SELECT     TOP (1) F1
FROM         WordList
WHERE     (F1 > '')
ORDER BY NEWID()), B AS (
SELECT     TOP (1) F2
FROM         WordList
WHERE     (F2 > '')
ORDER BY NEWID()
)

select * from A,B
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 150 total points
ID: 24134279
In a simple manner:

SELECT (SELECT     TOP (1) F1 FROM  WordList WHERE     (F1 > '') ORDER BY NEWID()) F1,
(SELECT     TOP (1) F2 FROM WordList WHERE (F2 > '') ORDER BY NEWID()) F2
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 200 total points
ID: 24134536
Yeah, go for the second one.

But... if your WordList is a large table, ordering by newid() is potentially very expensive. You're generating a new guid for each row, and then going through that list looking for the smallest random value.

It might be better to consider generating a single random number and then finding that row.

Rob
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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