pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Not able to find an alternative for SELECT TOP n WITH TIES in postgresql which is available in sql server.
2+ messages / 2 participants
[nested] [flat]
* Re: Not able to find an alternative for SELECT TOP n WITH TIES in postgresql which is available in sql server.
@ 2017-05-20 00:14 Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 2+ messages in thread
From: Alvaro Herrera @ 2017-05-20 00:14 UTC (permalink / raw)
To: MAJUMDER, SAYAN <sayan.a.majumder@capgemini.com>; +Cc: pgsql-admin
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. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Re: Not able to find an alternative for SELECT TOP n WITH TIES in postgresql which is available in sql server.
@ 2017-05-20 00:32 David G. Johnston <david.g.johnston@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2017-05-20 00:32 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: MAJUMDER, SAYAN <sayan.a.majumder@capgemini.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-05-20 00:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-20 00:14 Re: Not able to find an alternative for SELECT TOP n WITH TIES in postgresql which is available in sql server. Alvaro Herrera <alvherre@2ndquadrant.com>
2017-05-20 00:32 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox