# Thread: max rowcount on fields

1. Registered User
Join Date
Feb 2004
Posts
492

I have an sp that returns data to a client application. I wonder how I'd go about doing the following: I need to implement a maximum amount of rows on a combination of several columns that are part of the resultset.

fe; Col001, Col002, Col003 are the result, the unique count of Col001 and Col002 are two;

Col001, Col002, Col003
1, 'aa', 'someOtherValue'
1, 'aa', 'someThingElse'
1, 'aa', 'ratherSillyActually'
2, 'aa', '...'

I'd return:
1, 'aa', 'someOtherValue'
1, 'aa', 'someThingElse'
2, 'aa', '...'

Any idea's?
Last edited by Kaiowas; 01-21-05 at 06:05. Reason: double post

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by Kaiowas
the unique count of Col001 and Col002 are two;
Huh?

Then it becomes 3?

I'm confused (My natural state)

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
So you are looking for something like TOP, but for a combination of values rather than for the entire result set? This can be done, but you will need to have some sort order defined. SQL Server does not deal well with arbitrary logic: "pick any three of all the possible results".

4. Registered User
Join Date
Feb 2004
Posts
492
Incase there's a row: 3, 'aa', '....' then that would also appear on the output.

So far, I've setup a subselect joining itself matching the columns that have the maxcount (Col001, Col002). The sub does a top, so in effect this works.

Code:
```use monkey
go

set nocount on
go

create table #temp (Col00X integer identity(1,1), Col001 integer, Col002 varchar(5), Col003 varchar(30))
go
insert into #temp (Col001, Col002, Col003)
(select 1,     'aa',   'someOtherValue' union select 1, 'aa', 'someThingElse' union select 1, 'aa', 'ratherSillyActually' union select 2, 'aa', '...')
go

select * from #temp

select t1.*
from #temp t1
where Col00X in (
( select top 2 Col00X
from #temp t2
where t2.Col001 = t1.Col001
order by t2.Col00X)
)

go
drop table #temp
go```

However, performance is down the drain (somewhere way down under I'd say).

5. Registered User
Join Date
Feb 2004
Posts
492
and oh... the top 2 is fixed here.. but not in real life...

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
So you want TOP N, where N is determined at runtime? You can't pass a variable to TOP, so you would have to do your whole statement as dynamic sql .

7. Registered User
Join Date
Feb 2004
Posts
492
jup! aint it grand?
and where it took just seconds to haul over 10000 rows, it now takes longer than coffee-and-a-chit-chat-stare-out-the-window-for-an-hour-or-so time.

So.. I think I'll have to setup an alternate table with prework done already to if I want to keep things going at all...

8. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
No, you can do this relatively quickly return a dynamic number of records for each combination of columns, but you will have to define a sort order that will govern which records are returned.

9. Registered User
Join Date
Feb 2004
Posts
492
You mean write out _all_ of the columns joined in the sort order?

Did that.. in both the select AND the subselect.. works out nicely (better) when rows are like 7000 returning with 6 (01:33). What worries me is that the IN (..) could be filled with 0 to a million or more values so, I wonder how that performs.
Last edited by Kaiowas; 01-21-05 at 12:59.

10. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
No, that is not what I mean.

11. Registered User
Join Date
Feb 2004
Posts
492
Silly me, should have turned on my HAL9000! Takes a while to start, meanwhile, could you tell me what you did mean?

12. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
i think what we're trying to say is that TOP has no meaning unless it is in the context of sorting, i.e. ORDER BY something

in your code example, you're using a identity column for sorting, but that simply numebrs an arbitrary order

or are you saying "i don't care which two rows, just not more than two"?

in that case, why not use Col003 (see post #1) as your ORDER BY?

13. Registered User
Join Date
Feb 2004
Posts
492
aha! I care about which records are shown; the ones with the lowest date. So in the 'real' sp I do an order by on the date and identity column, but also include the other columns.

It just didn't speed up things as much as I was hoping for...

14. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
excuse me for not seeing which of Col001, Col002, Col003 was the date column

here is a solution (not the only solution, just another solution) to pick the top 2 rows by date from a table which has a composite primary key consisting of columns called, okay, what the heck, Col001 and Col002 --
Code:
```select t1.Col001
, t1.Col002
, t1.Col003
, t1.DateCol
from yourtable as t1
inner
join yourtable as t2
on t1.Col001 = t2.Col001
and t1.Col002 = t2.Col002
and t1.DateCol <= t2.DateCol
group
by t1.Col001
, t1.Col002
, t1.Col003
, t1.DateCol
having count(*) <= 2
order
by t1.Col001
, t1.Col002
, t1.DateCol desc```

15. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Now that you have finally gotten around to supplying the information I've been asking for...

Code:
```set nocount on

declare @TopN int
set	@TopN = 3

create table #KaiwasPost (Col001 int, Col002 char(2), Col003 varchar(20), SortDate datetime)
insert into #KaiwasPost select 1, 'aa', 'someOtherValue', '1/1/2005'
insert into #KaiwasPost select 1, 'aa', 'someThingElse', '1/2/2005'
insert into #KaiwasPost select 1, 'aa', 'ratherSillyActually', '1/3/2005'
insert into #KaiwasPost select 1, 'aa', 'blahblahblah', '1/4/2005'
insert into #KaiwasPost select 1, 'bb', 'alkfh', '1/5/2005'
insert into #KaiwasPost select 1, 'bb', 'wiyrwy', '1/6/2005'
insert into #KaiwasPost select 1, 'bb', 'oqiyr', '1/7/2005'
insert into #KaiwasPost select 1, 'bb', 'shoopy-doop', '1/8/2005'

select	A.Col001, A.Col002, A.Col003, A.SortDate, count(*)
from	#KaiwasPost A
inner join #KaiwasPost B
on A.Col001 = B.Col001
and A.Col002 = B.Col002
and A.SortDate >= B.SortDate
group by A.Col001, A.Col002, A.Col003, A.SortDate
having count(*) <= @TopN
order by A.Col001, A.Col002, A.SortDate

drop table #KaiwasPost```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•