如何在sql中获取最后一个Row_number（）的示例？

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