I have a table that has a ton of rows (>10K). Most of the rows have duplicate role values associated with the ques_id. I'am new to the sql. What I am trying to do is select rows by distinct AND latest ques_id added. Here is my table(tbl_questions) structure.
id | ques_id | question | ans
1 | 2 | HTML stands.. | 3
2 | 5 | PHP stands.. | 2
3 | 6 | CSS stands.. | 4
4 | 6 | CSS stands.. | 4
5 | 5 | PHP stands.. | 2
6 | 6 | CSS stands.. | 4
This would be the desired result:
id | ques_id | question | ans
1 | 2 | HTML stands.. | 3
5 | 5 | PHP stands.. | 2
6 | 6 | CSS stands.. | 4
Here are the query I've tried so far:
SELECT DISTINCT ques_id, question, ans FROM tbl_questions
Just an other perspective by giving a row number by group.
Query
select t1.id, t1.ques_id, t1.question, t1.ans from
(
select id, ques_id, question, ans,
(
case ques_id when @curA
then @curRow := @curRow + 1
else @curRow := 1 and @curA := ques_id end
) as rn
from tbl_questions t,
(select @curRow := 0, @curA := '') r
order by ques_id,id desc
)t1
where t1.rn = 1;
SQL Fiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With