SQL从表中选择不同的最后值

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.

我有一个有大量行(>10K)的表。大多数行具有与ques_id关联的重复角色值。我是sql新手。我要做的是选择不同的行，并添加最新的ques_id。这是我的表(tbl_questions)结构。

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

4 个解决方案

#1

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

SQL小提琴

#2

You want the latest row for each question? You can use NOT EXISTS to return those rows:

你想要每个问题的最近一行吗?您可以使用NOT exist返回这些行:

SELECT ques_id, question, ans
FROM tbl_questions t1
where not exists (select 1 from tbl_questions t2
                  where t2.ques_id = t1.ques_id
                    and t2.id > t1.id)

#3

SELECT a.* 
  FROM tbl_questions a 
  JOIN 
     ( SELECT ques_id
            , MAX(id) max_id 
         FROM tbl_questions 
        GROUP 
           BY ques_id
     ) b 
    ON b.max_id = a.id;

#4

Try this query

试试这个查询

SELECT 
SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC),',',1) AS i_d, 
ques_id, 
question,
SUBSTRING_INDEX(GROUP_CONCAT(ans ORDER BY id DESC),',',1) AS Answer 
FROM tbl_questions 
GROUP BY ques_id

Output

输出

i_d |ques_id | question       | Answer
1    2      HTML stands..       3
5    5      PHP stands..        2
6    6      CSS stands..        4

#1