For example, I have
例如,我有
tab1:
att1|att2
A|3
B|4
C|3
D|4
E|1
F|2
And I want
我希望
B|4
D|4
I know I can use
我知道我能用
SELECT att1 att2
FROM tab1 as T
WHERE T.att2 =
(
SELECT MAX(att2) FROM tab1;
}
I wondering if there is a more clever way to do it. B/c if it takes a complicated query to get tab1, it is cumbersome to write it twice. By the way, I am using Sqlite.
我想知道有没有更聪明的办法。B/c如果需要一个复杂的查询才能得到tab1，那么编写两次是很麻烦的。顺便说一下，我用的是Sqlite。
2 个解决方案
#1
2
My original answer was:
我最初的回答是:
SELECT att1, att2
FROM tab1
ORDER BY att2 DESC LIMIT 0,1;
I apologize, that won't work if there is more than one record with the max value. Here is another answer:
我很抱歉，如果有超过一条记录的最大值，那是行不通的。这是另一个答案:
SELECT t1.att1, t1.att2
FROM tab1 AS t1
LEFT JOIN tab1 AS t2
ON t1.att2 < t2.att2
WHERE t2.att1 IS NULL;
It's questionable whether this form is more efficient than nested SELECT the author mentioned, but might be better in case if tab1 is a select itself.
这个表单是否比上面提到的嵌套SELECT更有效值得怀疑，但如果表1本身就是一个SELECT，那么它可能会更好。
#2
1
From what I understood, the problem is not the query itself but "cumbersome" writing table tab1. I would suggest then you create view tab1, and then use it.
根据我的理解，问题不是查询本身，而是“麻烦”的写入表tab1。我建议您创建view tab1，然后使用它。
#1
2
My original answer was:
我最初的回答是:
SELECT att1, att2
FROM tab1
ORDER BY att2 DESC LIMIT 0,1;
I apologize, that won't work if there is more than one record with the max value. Here is another answer:
我很抱歉，如果有超过一条记录的最大值，那是行不通的。这是另一个答案:
SELECT t1.att1, t1.att2
FROM tab1 AS t1
LEFT JOIN tab1 AS t2
ON t1.att2 < t2.att2
WHERE t2.att1 IS NULL;
It's questionable whether this form is more efficient than nested SELECT the author mentioned, but might be better in case if tab1 is a select itself.
这个表单是否比上面提到的嵌套SELECT更有效值得怀疑，但如果表1本身就是一个SELECT，那么它可能会更好。
#2
1
From what I understood, the problem is not the query itself but "cumbersome" writing table tab1. I would suggest then you create view tab1, and then use it.
根据我的理解，问题不是查询本身，而是“麻烦”的写入表tab1。我建议您创建view tab1，然后使用它。