PostgreSQL Adds Fetch First with Ties.. Just Like Top N with Ties in SQL Server

栏目: IT技术 · 发布时间: 4年前

内容简介:PostgreSQL 13 Beta 1 was released yesterday, you can read the release notes hereOne thing that caught my eye was this statement in the release notes

PostgreSQL 13 Beta 1 was released yesterday, you can read the release notes here

https://www.postgresql.org/about/news/2040/

One thing that caught my eye was this statement in the release notes

PostgreSQL 13 brings more convenience to writing queries with features like FETCH FIRST WITH TIES, which returns any additional rows that match the last row.

This is I guess exactly like TOP WITH TIES in SQL Server. I believe this has been around in SQL Server since at least version 7.  How many times have I used it in code that was deployed in the last 20 years?  I believe I have used WITH TIES only once. It does make for great interview questions and SQL puzzles  :-)

So let's take a quick look at how TOP WITH TIES works in SQL Server.  The first thing we will do is look at what Books On Line says about TOP


WITH TIES  Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows. You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you've also specified the ORDER BY clause. The returned order of tying records is arbitrary. ORDER BY doesn't affect this rule.

Time to get started and write some code to see this in action

First create this table of students and insert some data

CREATE TABLE #TopExample(GradeAverage int, Student varchar(100))
INSERT #TopExample VALUES(99.00,'Plato'),
      (98,'Socrates'),
      (95,'Diogenes the Cynic'),
      (94,'Antisthenes'),
      (94,'Demetrius'),
      (50,'Denis')

As you can see, I am not a very good student  :-(

If you do a regular TOP 4 query like this

SELECT TOP 4 GradeAverage, Student 
FROM #TopExample  
ORDER BY GradeAverage DESC

You will get back these results

GradeAverage Student
99          Plato
98          Socrates
95          Diogenes the Cynic
94          Demetrius

As you can see we are missing another student with a grade of 94, this is Antisthenes

This is easily fixed by adding WITH TIES to the query

SELECT TOP 4 WITH TIES GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC

Now, you will get back these results, as you can see, you now have 5 rows and both rows with a grade average of 94 are included

GradeAverage Student
99          Plato
98          Socrates
95          Diogenes the Cynic
94          Demetrius
94          Antisthenes

Another way to do the same as WITH TIES is by using DENSE_RANK.  That query looks like this

;WITH c AS (SELECT DENSE_RANK() OVER (ORDER BY GradeAverage DESC) AS dens, 
 GradeAverage,Student 
 FROM #TopExample)

SELECT GradeAverage, Student 
FROM c WHERE dens <=4
ORDER BY GradeAverage DESC

You will get back these same results again, you now have 5 rows and both rows with a grade average of 94 are included as well

GradeAverage Student
99          Plato
98          Socrates
95          Diogenes the Cynic
94          Demetrius
94          Antisthenes

Using DENSE_RANK is bit more code, but if portability is a concern, it might be a better choice

There you go a post about a feature you will never use :-)

If you want to ran all the queries in one shot here is all the code


CREATE TABLE #TopExample(GradeAverage int, Student varchar(100))
INSERT #TopExample VALUES(99.00,'Plato'),
      (98.00,'Socrates'),
      (95.00,'Diogenes the Cynic'),
      (94.00,'Antisthenes'),
      (94.00,'Demetrius'),
      (50.00,'Denis')

SELECT TOP 4 GradeAverage, Student 
FROM #TopExample  
ORDER BY GradeAverage DESC

SELECT TOP 4 WITH TIES GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC

;WITH c AS (SELECT DENSE_RANK() OVER (ORDER BY GradeAverage DESC) AS dens, 
 GradeAverage,Student 
 FROM #TopExample)

SELECT GradeAverage, Student 
FROM c WHERE dens <=4
ORDER BY GradeAverage DESC 


DROP TABLE #TopExample

And here is what it all looks like in SSMS, code and output



PostgreSQL Adds Fetch First with Ties.. Just Like Top N with Ties in SQL Server


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

算法之美

算法之美

左飞 / 电子工业出版社 / 2016-3 / 79.00元

《算法之美——隐匿在数据结构背后的原理(C++版)》围绕算法与数据结构这个话题,循序渐进、深入浅出地介绍了现代计算机技术中常用的40 余个经典算法,以及回溯法、分治法、贪婪法和动态规划等算法设计思想。在此过程中,《算法之美——隐匿在数据结构背后的原理(C++版)》也系统地讲解了链表(包括单向链表、单向循环链表和双向循环链表)、栈、队列(包括普通队列和优先级队列)、树(包括二叉树、哈夫曼树、堆、红黑......一起来看看 《算法之美》 这本书的介绍吧!

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具