By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 916 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

What would my SQL statement look like?

P: 33
Here is a prototype statement, which if it worked would give me a desirable result. Unfortunately random doesn't exist as an aggregate function. The next best thing I can find that does work is the first and last functions, but they aren't as good.

Expand|Select|Wrap|Line Numbers
  1. select id, test, random(result) 
  2. from labs 
  3. group by id, test 
  4.  
In case some background is needed. Each person takes several tests, sometimes they take a single test more than once. If this is the case, I only want to include one test result, and I want that result to be randomly selected. Any ideas?
Jun 4 '07 #1
Share this Question
Share on Google+
1 Reply


P: 33
I figured it out:

Expand|Select|Wrap|Line Numbers
  1. select a.id, a.test,
  2.     (select result 
  3.     from labs b 
  4.     where a.id = b.id and a.test = b.test 
  5.     order by random()
  6.     limit 1) as result
  7. from labs a
  8. group by a.id, a.test order by a.id,a.test
Jun 4 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.