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

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, May 19, 2017 at 5:14 PM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> 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.​

​