Top n records by column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Top n records by column

Author

Topic

demifelix
Starting Member

22 Posts

Posted - 2007-10-13 : 00:27:14

I posted this same question in SQL server 2005 forums and just found out that mine is actually running in SQL server 2000 environment, so I post it here again.

I'm trying to select top 3 amounts in descending order by year. I read some of the posts already and tried to apply to my situation. The problem is: I have 72K records in this table, and neither one of my queries below ever come back.

Table name: tblAmount
Year Amount
2000 1
2000 2
2000 3
2000 4
2001 6
2001 7
2001 8
2001 9

I used both these queries below which took forever to execute:
Query 1:

select Year, Amount
from tblAmount a
where a.Amount in (
select top 3 Amount
from tblAmount where Year= a.Year
order by Year, net_Amount desc)
order by Year, Amount

Query 2:

select Amount, Year
from tblAmount a
where (select count(distinct Amount)
from tblAmount
where Year= a.Year
and Amount >= a.Amount) <=3
order by Amount, Year

Thanks for all your help.

Kristen
Test

22859 Posts

Posted - 2007-10-13 : 10:30:03

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Second point

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-13 : 12:17:09

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90925

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

demifelix
Starting Member

22 Posts

Posted - 2007-10-15 : 08:45:24

quote:
Originally posted by Kristen

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Second point

Kristen

Thanks Kristen. However, this does not produce what I am looking for: it does not return the top 5 amount by year. In your example, the Rank column come back sorted in desc order, but somehow it excludes the top 5 ranks (A = 95, 80, 73, ...). Can you show me how to twist this to make it works correctly?

Results from your query:
A d 46
A e 37
A c 14
A j 3
A a 2
B b 44
B j 31
B e 28
B a 18
B f 6

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 08:50:10

please post the query you are now trying.

It would help if you also post a CREATE TABLE statement and some INSERTS for the data so we don't all have to do that individually.

Here's an example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20to%20ask%20a%20question%20on%20SQL%20Team%20and%20get%20a%20quick%20answer

Kristen

demifelix
Starting Member

22 Posts

Posted - 2007-10-15 : 08:52:51

[quote]Originally posted by dinakar

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90925

SELECT [year], Amount
FROM YourTable T
WHERE (
SELECT count(*)
FROM YourTable T2
WHERE T2.[Year] = T.[Year] AND T2.Amount > T.Amount
) <= 2
ORDER BY [Year] ASC, Amount DESC

Hi dinakar,

Your query above is almost the same as my query #2 above, and it does not return either. I let it run for 2 hours and I have to cancel the job. The table only has 75K records by the way, not a big deal for SQL server. This query works with small table (I created a sample table of 20 records and it works) but not with my real data. Any idea dinakar? Thanks again for looking into this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 08:53:16

Now this is very simple TOP x WITHIN GROUP, so this technique may be overkill

-- Prepare sample data
DECLARE	@tblAmount TABLE ([Year] SMALLINT, Amount INT)

INSERT	@tblAmount
SELECT	2000, 1 UNION ALL
SELECT	2000, 2 UNION ALL
SELECT	2000, 3 UNION ALL
SELECT	2000, 4 UNION ALL
SELECT	2001, 6 UNION ALL
SELECT	2001, 7 UNION ALL
SELECT	2001, 8 UNION ALL
SELECT	2001, 9

-- Prepare staging
DECLARE	@Stage TABLE (RowID INT IDENTITY(1, 1), [Year] SMALLINT, Amount INT)

INSERT		@Stage
SELECT		[Year],
		Amount
FROM		@tblAmount
ORDER BY	[Year],
		Amount DESC

-- Get the wanted records back
SELECT		s.[Year],
		s.Amount
FROM		@Stage AS s
INNER JOIN	(
			SELECT		MIN(RowID) + 0 AS MinID,
					MIN(RowID) + 2 AS MaxID,
					[Year]
			FROM		@Stage
			GROUP BY	[Year]
		) AS d ON d.[Year] = s.[Year]
WHERE		s.RowID BETWEEN d.MinID AND d.MaxID

E 12°55'05.25"
N 56°04'39.16"

demifelix
Starting Member

22 Posts

Posted - 2007-10-15 : 09:01:36

quote:
Originally posted by Kristen

please post the query you are now trying.

It would help if you also post a CREATE TABLE statement and some INSERTS for the data so we don't all have to do that individually.

Kristen

Would something like this work? The queries I used are query #1 and query #2 posted originally at the beginning of this thread. Thanks for looking into this Kristen.

Declare @tblAmount table (year int, amount int )
Insert Into @tblAmount
Select 2000, 2
Union All Select 2000, 3
Union All Select 2000, 4
Union All Select 2000, 5
Union All Select 2000, 6
Union All Select 2000, 7
Union All Select 2000, 8
Union All Select 2000, 9
Union All Select 2000, 10
Union All Select 2001, 3
Union All Select 2001, 4
Union All Select 2001, 5
Union All Select 2001, 6
Union All Select 2001, 7
Union All Select 2001, 8
Union All Select 2001, 9
Union All Select 2002, 5
Union All Select 2002, 6
Union All Select 2002, 7
Union All Select 2002, 8
Union All Select 2002, 9
Union All Select 2002, 10

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 11:52:40

OK, I've got the data, but you said that the query I pointed you at did not work. I need the exact query you tried - i.e.edited to work with the @tblAmount you just posted, and then a question about what is broken (or perhaps, judging from your earlier comments, that it works fine but is Dog Slow!)

Pointing my vaguely at other queries in this thread means I have to then modify them to work with the table you have just posted, and I may very well then be doing something different to you, which would be a waste of my time.

Peso may have time to generate data, I'm afraid I don't, and if half a dozen of us all start writing the same queries we'll get royally chessed off to have wasted our time

So:

Create table? Check!
Insert data? Check!
Query that is malfuncitoning? Not yet.
Expected results? Not yet.

Kristen

demifelix
Starting Member

22 Posts

Posted - 2007-10-16 : 11:04:25

quote:
Originally posted by Kristen
So:

Create table? Check!
Insert data? Check!
Query that is malfuncitoning? Not yet.
Expected results? Not yet.

Kristen

Here you go:
Declare @tblAmount table (year int, amount int )
Insert Into @tblAmount
Select 2000, 2
Union All Select 2000, 3
Union All Select 2000, 4
Union All Select 2000, 5
Union All Select 2000, 6
Union All Select 2000, 7
Union All Select 2000, 8
Union All Select 2000, 9
Union All Select 2000, 10
Union All Select 2001, 3
Union All Select 2001, 4
Union All Select 2001, 5
Union All Select 2001, 6
Union All Select 2001, 7
Union All Select 2001, 8
Union All Select 2001, 9
Union All Select 2002, 5
Union All Select 2002, 6
Union All Select 2002, 7
Union All Select 2002, 8
Union All Select 2002, 9
Union All Select 2002, 10

--Query 1:

select Year, Amount
from @tblAmount a
where a.Amount in (
select top 3 Amount
from @tblAmount where Year= a.Year
order by Year, Amount desc)
order by Year, Amount desc

-- Query 2:
select Amount, Year
from @tblAmount a
where (select count(distinct Amount)
from @tblAmount
where Year= a.Year
and Amount >= a.Amount) <=3
order by Year, amount desc

My point is -- both these queries work exactly as I want for this small dataset, but none of them return when I run them with 75K records. Very strange since 75K records should not cause any problems in SQL Server. Thanks Kristen.

demifelix
Starting Member

22 Posts

Posted - 2007-10-16 : 11:10:16

quote:
Originally posted by Peso

Now this is very simple TOP x WITHIN GROUP, so this technique may be overkill[code][green]--

Thanks Peso, this works. You call this query SIMPLE? To me this stuff is very advanced. I don't know when I'll be able to do all this myself.

Just a thought, do you happen to know why the other 2 queries that I used don't ever return? They work for small number of records only. The queries seem ok (to me, I guess), but why taking forever to execute?

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 11:16:57

OK, I can see the queries now, thanks.

The approach you have taken is going to be CPU intensive. For each row found do a separate SELECT to find if that row is in the TOP 3. So for a value that occurs, say, 100 times then EACH of those is going to trigger a TOP 3 query on the same 100 rows to see if its in the TOP 3. It would be better if you eliminated 97 of those without checking them!!

So you need to make a list of the "top 3" for each value, and then JOIN to that. Which is what, in effect, Peso's nested sub-select is doing [much the same thing as preparing the "inner query" first, and then just JOINing to it]

Kristen

demifelix
Starting Member

22 Posts

Posted - 2007-10-17 : 09:00:22

Thanks Kristen. That really helps to explain the reason.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:08:29

Or swithch to SQL Server 2005

;WITH Yak ([Year], Amount, RecID)
AS (
	SELECT	[Year],
		Amount,
		ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY Amount DESC) AS RecID
	FROM	@tblAmount
)

SELECT	[Year],
	Amount
FROM	Yak
WHERE	RecID <= 3

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 12:02:46

";WITH Yak"

Classic!!!!!!!!!!!!!

demifelix
Starting Member

22 Posts

Posted - 2007-10-23 : 11:42:37

Hi Kristen, Peso,

First of all, thanks again for your help with this top n query. It worked great until I added another criteria to my query ... now I want to sum up all the amounts by Employee ID (Empid) for that year and get top 3 Amounts by Year and by Empid. For example, in the year 2000, Empid 115 should be on the top of the list because he has Amount of 15, followed by Empid 113 with Amount of 11, and then Empid 117 last with Amount of 10. I have pasted data and the query I used, but it comes back with an error ".....IDENTITY_INSERT is ON". Could you please help again.
Thanks.

-----Query used----
Declare @tblAmount table (year int, empid int, amount int )
Insert Into @tblAmount
Select 2000, 111, 2
Union All Select 2000, 111, 3
Union All Select 2000, 112, 4
Union All Select 2000, 113, 5
Union All Select 2000, 113, 6
Union All Select 2000, 115, 7
Union All Select 2000, 115, 8
Union All Select 2000, 116, 9
Union All Select 2000, 117, 10
Union All Select 2001, 111, 3
Union All Select 2001, 112, 4
Union All Select 2001, 113, 5
Union All Select 2001, 114, 6
Union All Select 2001, 114, 7
Union All Select 2001, 115, 8
Union All Select 2001, 116, 9
Union All Select 2002, 111, 5
Union All Select 2002, 112, 6
Union All Select 2002, 112, 7
Union All Select 2002, 113, 8
Union All Select 2002, 114, 9
Union All Select 2002, 115, 10

-- Prepare staging
DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), [Year] SMALLINT, empid INT, Amount INT)

INSERT @Stage
SELECT [Year], Empid, sum(Amount)
FROM @tblAmount
group by [Year], Empid
ORDER BY [Year], empid, Amount DESC

-- Get the wanted records back
SELECT s.[Year], s.empid, s.Amount
FROM @Stage AS s
INNER JOIN (
SELECT MIN(RowID) + 0 AS MinID,
MIN(RowID) + 2 AS MaxID,
[Year], empid
FROM @Stage
GROUP BY [Year], empid
) AS d ON d.[Year] = s.[Year] and d.empid = s.empid
WHERE s.RowID BETWEEN d.MinID AND d.MaxID

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 12:37:12

[code]
INSERT @Stage([Year], empid, Amount)
SELECT [Year], Empid, sum(Amount)
FROM @tblAmount
group by [Year], Empid
ORDER BY [Year], empid --, Amount DESC
[/code]
Why you trying to sort by "Amount DESC" - You are aggregating to create a SUM, so the sort on Amount is not valid - but it suggests that maybe you were after an answer to a different question

Kristen

demifelix
Starting Member

22 Posts

Posted - 2007-10-23 : 13:53:11

quote:
Originally posted by Kristen

INSERT @Stage([Year], empid, Amount)
SELECT [Year], Empid, sum(Amount)
FROM @tblAmount
group by [Year], Empid
ORDER BY [Year], empid --, Amount DESC

Why you trying to sort by "Amount DESC" - You are aggregating to create a SUM, so the sort on Amount is not valid - but it suggests that maybe you were after an answer to a different question

Kristen

Well, my purpose is to pull in top amount by EmpID for each year and sorted by amount so that the largest amount is on top of the list -- so I can know who's the best of the best -- that sort of idea. If I take the Order By "Amount DESC" out then the records coming back in my second half query will not be in any order.

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 14:06:31

In that case you probably need

ORDER BY [Year], empid, SUM(Amount) DESC

Kristen

demifelix
Starting Member

22 Posts

Posted - 2007-10-30 : 08:21:00

Thanks Kristen, let me try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources