Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL select distinct last values from table

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

1 Answers

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

like image 121
Ullas Avatar answered Mar 24 '26 01:03

Ullas



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!