Postgres Pro
Downloads
Home   >   mailing lists

Re: [ADMIN] Re: Not able to find an alternative for SELECT TOP n WITHTIES in postgresql which is available in sql server. - Mailing list pgsql-admin

From David G. Johnston
Subject Re: [ADMIN] Re: Not able to find an alternative for SELECT TOP n WITHTIES in postgresql which is available in sql server.
Date
Msg-id CAKFQuwYCrHfszvko60Z3rN6=_eye4hk3Ng-tcA=72UJ7yANaQA@mail.gmail.com
Whole thread Raw
In response to [ADMIN] Re: Not able to find an alternative for SELECT TOP n WITH TIES inpostgresql which is available in sql server.  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-admin
Tree view
On Fri, May 19, 2017 at 5:14 PM, Alvaro Herrera <alvherre@2ndquadrant.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.​
​

pgsql-admin by date:

Previous
From: Alvaro Herrera
Date:
Subject: [ADMIN] Re: Not able to find an alternative for SELECT TOP n WITH TIES inpostgresql which is available in sql server.
Next
From: Sumeet Shukla
Date:
Subject: Re: [ADMIN] error installing oracle_fdw extension