-
Aug 28th, 2014, 08:53 AM
#1
Thread Starter
Fanatic Member
Php sql help
i have a table called tbl_questions
in this table i have a field called Category
and this category field contain value like
A
B
C
D
E
2
3
4
5
55
ZA
GF
KO
PL
4F
My question is if i want to
select 5 records from category B
select 10 records from category 55
select 8 records from category J
So what should be my SQL query?
Do i need to use UNION? Or there is any option smaller option in query?
-
Aug 28th, 2014, 10:07 AM
#2
Re: Php sql help
so you have two tables? Categories and tbl_Questions?
what's in the Questions table? What does it look like?
Also, this is more of a SQL question so I'm going to ask the mods to move it there.
-tg
-
Aug 28th, 2014, 10:29 AM
#3
Re: Php sql help
Moderator Action: Moved thread to Database Development.
-
Aug 28th, 2014, 10:44 AM
#4
Thread Starter
Fanatic Member
Re: Php sql help
No there is only single table tblquestions
It has few columns like
Question
Title
Etc...
But column category contain 1 category in one record...
Like
A
B
M
K
P
O
U
4
5
6
7
77
33
4r
-
Aug 28th, 2014, 11:30 AM
#5
Re: Php sql help
so,,, what's the problem... select your questions based on WHERE Category = what ever category type you want.
-tg
-
Aug 28th, 2014, 11:35 AM
#6
Re: Php sql help
if you want to have all 23 questions to be returned in one sql then yes, you have to use top x union with three different where clauses. it sounds like you prob. want RANDOM questions returned, then there is also a way where you order by a random number. i do not need this often nor have i ever used it in mysql so you would need to google that.
-
Aug 29th, 2014, 05:55 AM
#7
Re: Php sql help
The easiest, and fairly counter intuitive way, is to order by a NewID(). Because a NewID gives you a random guid it gives you a random order.
So you want something like (nb typed straight in so beware of syntax errors):-
Code:
Select Question, Title
From (Select Top 5 Question, Title
From tbl_Questions
Where Category = 'B'
Order By NewID())
Union All
Select Question, Title
From (Select Top 10 Question, Title
From tbl_Questions
Where Category = '55'
Order By NewID())
Union AllSelect Question, Title
From (Select Top 8 Question, Title
From tbl_Questions
Where Category = 'J'
Order By NewID())
Order By NewID()
You need the inner selects because you can't apply an order by to the the indivdual parts of a union, only the overall set. And I've assumed you'd want the order of the overall set randomized as well, if not drop the last OrderBy.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 29th, 2014, 06:32 AM
#8
Re: Php sql help
Actually... before that... we need to know what the database system is. Is it SQLServer or MySQL? Since this was originally in the PHP section, I'm guessing it's more likely to be MySQL, which means instead of TOP, a LIMIT would need to be used. Also I'm not sure newId is available in MySQL, I'm sure there is SOME fuinction available to do the same thing, just not specifically newid().
That said, the solution would be fine if all you ever need is three sections. What if you need 2, or 5?
I'd use a single query that returned rows for a single category. Then pass in the row limit count and the category as a parameter... then if I need to run it for two categories, cal it twice, once for one category, and another time for hte other category.
-tg
-
Aug 29th, 2014, 11:34 AM
#9
Re: Php sql help
we need to know what the database system is
Good Point! I didn't think to stop and check.
I don't think it would be possible to write a single query to resolve the whole problem if there was a variable number of categories (though a bit of me is wondering if you couldn't do something with a cte - just can't seem to formulate it myself). If that was the requirement I think I'd agree with TG, write it as a table valued function and call it from an sp.
If you do want to randomize the result and if you are using sql server you might also want to look at the TABLESAMPLE keyword. It's not truly random but it's likely random enough and it's more performant over large data sets.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|