Re: Re: Not able to find an alternative for SELECT TOP n WITH TIES in postgresql which is available in sql server.

On Fri, May 19, 2017 at 5:14 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> I think the answer to your question is to
> use a window function.
>
> MAJUMDER, SAYAN wrote on April 24th 2017:
>
> > Hi,
> > I am new to postgresql and we have a query in sql server that is SELECT
> TOP n WITH TIES column_name from table_name.
> > This clause is used to retrieve all similar rows to a base result set.
> > I am not able to find any similar clause in postgresql.
> >
> > Example in sql server:-
> > We have a table with 6 entries 1 to 4 and 5 twice.
> >
> > SELECT TOP 5 WITH TIES *
> > FROM MyTable
> > ORDER BY ID;
> >
> > This will returns 6 rows, as the last row is tied (exists more than
> once.)
> >

​Alvaro is right - though you also need a subquery.​

​​select * from (
select dense_rank() OVER (ORDER BY v)
from (values (1),(2),(3),(4),(5),(5)) vals (v)
) src where dense_rank < 6

​David J.​
​