My table example:
我的表示例:
I know that if I wanted to grab the highest score from each company I would say:
我知道,如果我想从每家公司获得最高分,我会说:
where columnName = 1
but how do I grab the one at the end without putting columnName = 4 because I want the highest and least and each company will a different amount of scores.
但是如何在不使用columnName = 4的情况下抓住最后一个,因为我想要最高和最低,每个公司将获得不同的分数。
3 个解决方案
#1
If you're looking for the highest and lowest scores, here's one option:
如果您正在寻找最高和最低分,这里有一个选项:
with cte as (
select row_number() over (partition by company order by score) minscore,
row_number() over (partition by company order by score desc) maxscore,
company, score
from yourtable
)
select company, score
from cte
where minscore = 1 or maxscore = 1
- SQL Fiddle Demo
SQL小提琴演示
#2
Your initial query used the order by DESC to bring the highest first .. just reverse that: Leave it ASC and the order will be reversed .. again pick the =1 item ..
您的初始查询使用DESC的订单带来最高的第一个..只是反向:保持ASC并且订单将被颠倒..再次选择= 1项..
select ROW_NUMBER() OVER (partition by companyname order by weightedscore ASC ) ...
#3
This is for MySQL
这是针对MySQL的
SELECT MAX(WeightedScore),
MIN(WeightedScore)
FROM table
GROUP BY CompanyName;
For SQL Server refer https://*.com/a/1299598/4576237
对于SQL Server,请参阅https://*.com/a/1299598/4576237
#1
If you're looking for the highest and lowest scores, here's one option:
如果您正在寻找最高和最低分,这里有一个选项:
with cte as (
select row_number() over (partition by company order by score) minscore,
row_number() over (partition by company order by score desc) maxscore,
company, score
from yourtable
)
select company, score
from cte
where minscore = 1 or maxscore = 1
- SQL Fiddle Demo
SQL小提琴演示
#2
Your initial query used the order by DESC to bring the highest first .. just reverse that: Leave it ASC and the order will be reversed .. again pick the =1 item ..
您的初始查询使用DESC的订单带来最高的第一个..只是反向:保持ASC并且订单将被颠倒..再次选择= 1项..
select ROW_NUMBER() OVER (partition by companyname order by weightedscore ASC ) ...
#3
This is for MySQL
这是针对MySQL的
SELECT MAX(WeightedScore),
MIN(WeightedScore)
FROM table
GROUP BY CompanyName;
For SQL Server refer https://*.com/a/1299598/4576237
对于SQL Server,请参阅https://*.com/a/1299598/4576237