TOP N records

Question

Post reply

TOP N records

Amith Vemuganti

Ten Centuries

Points: 1350
More actions
October 7, 2008 at 3:38 pm

#193986

Hello All,
My requirement is as follows:
I want to pull TOP N records from a customer table order by CustomerID. For example, I'll be pulling TOP 2000 records from the table normally, but in some special cases, where the CustomerID in 2001 record matches with the 2000th record, I want to pull more than 2000 records (2001 records).
Could any one please let me know how I can achieve this using a single SQL.
Thanks in advance,
-Amith Vemuganti

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Like this:

declare @rows int

set @rows = 2001

select TOP (@rows) *

From master.sys.system_columns

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

If you are looking to pull the top 2000 values, including any duplicates of those values, you might care to combine what Barry was demonstrating with the WITH TIES clause.

So -

declare @rows int

set @rows = 2000

select TOP (@rows) WITH TIES *

From myTable

ORDER BY CustomerID

This will pull the first 2000 VALUES for customerID, and return any rows that match those.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Amith Vemuganti Ten Centuries Points: 1350 More actions · Answer 3

Amith Vemuganti

Ten Centuries

Points: 1350

October 8, 2008 at 8:05 am

#882289

Miller/Barry,

Thanks so much! Its working.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Glad we could help!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Matt Miller (10/7/2008)
select TOP (@rows) WITH TIES *
From myTable
ORDER BY CustomerID
This will pull the first 2000 VALUES for customerID, and return any rows that match those.

This is a little confusing, although probably it was meant alright... it does not pull first 2000 distinct values. It returns additional rows only if rows, that otherwise would not be displayed, have the same "rank" (i.e. same values in ORDER BY columns) as the last displayed row. That means, it works precisely like normal TOP, only it checks additionally for rows that are tied with the last one. It isn't easy to explain, an example would be better :

CREATE TABLE #test(tid INT, tvalue VARCHAR(10), seq INT)

INSERT INTO #test(tid, tvalue, seq)

SELECT 1, 'Goody', 1 UNION

SELECT 1, 'Two', 2 UNION

SELECT 1, 'Shoes', 3 UNION

SELECT 2, 'Stand', 1 UNION

SELECT 2, 'And', 2 UNION

SELECT 2, 'Deliver', 3

SELECT tid, tvalue FROM

(SELECT TOP 2 WITH TIES *

FROM #test

ORDER BY tid) as Q

ORDER BY Q.tid, Q.seq

DROP TABLE #test

According to what Matt wrote, all 6 rows should be returned with TOP 2 WITH TIES, but in fact only 3 are really returned. Anyway, I think this is precisely what was required 🙂

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

You're right - it's easier to describe with an example!:)

Thanks for the assist.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?