Thread: [ADMIN] Re: Not able to find an alternative for SELECT TOP n WITH TIES inpostgresql which is available in sql server.
[ADMIN] Re: Not able to find an alternative for SELECT TOP n WITH TIES inpostgresql which is available in sql server.
From
Alvaro Herrera
Date:
Hi Sayan, I have CCed the pgsql-admin list here, in the hope that somebody might reply (full question below). I think the answer to your question is to use a window function. Please make sure to always write to pgsql-admin@postgresql.org, not the "-owner" address. Otherwise, your message never reaches the list. 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.) > > Whereas > > > SELECT TOP 5 * > > FROM MyTable > > ORDER BY ID; > > This will returns 5 rows as the last row is tied. > > > --------------------------------------------------------------------------------------------------------------------------------------------------------------- > [Email_CBE.gif]Thanks and regards, > Sayan Majumder > CSD | AppsTwo | Analyst A4 > Capgemini Technology Services India Limited| Kolkata | > Tel.: +91 8584022251/ Ext.:- 3339161 > www.capgemini.com<http://www.capgemini.com/> > People matter, results count. > > > This message contains information that may be privileged or confidential and is the property of the Capgemini Group. Itis intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorizedto read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receivethis message in error, please notify the sender immediately and delete all copies of this message. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: [ADMIN] Re: Not able to find an alternative for SELECT TOP n WITHTIES in postgresql which is available in sql server.
From
"David G. Johnston"
Date:
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.