tsql issues - 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

Transact-SQL (2000)

tsql issues

Author

Topic

bouwob
Starting Member

3 Posts

Posted - 2006-05-22 : 11:24:29

I have the following example table records

pubdateid reelid Icount
1----------1-------1
1----------2-------2
1----------3-------3
1----------4-------4
2----------1-------4
2----------2-------3
2----------3-------2
2----------4-------1
3----------1-------1
3----------2-------1
3----------3-------2
3----------4-------2

And from this I need to fetch the highlighted rows. I need each pubdateid row that has the lowest amount in count. If the count has more than one that is the the lowest but the same as another I need to take the record with the highest reelid.

So far I have come up with this (minus the equal count part)


declare @tempstr varchar(8000)
declare @pubdateIDs varchar(3000)

set @pubdateIDs = '8347151, 4, 7608501'


set @tempstr = 'select pubdateid, reelid, min(Icount) from #MR_temp where pubdateid in (' + @pubdateids + ') group by pubdateid, reelid' 
exec (@tempstr)

Which I assumed would give me a list of all the pubdatids with the lowest count but instead just returns the entire table.

The groupby is destring my chosen values. Is there any way to eliminate the groupby and still let it run?
Any ideas????

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 11:33:14

select *
from tbl t
where Icount = (select top 1 Icount form tbl t2 where t2.pubdateid = t.pubdateid order by Icount, reelid desc)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bouwob
Starting Member

3 Posts

Posted - 2006-05-22 : 11:41:02

Wow you just saved me an addition several hours trying to figure this out. Much obliged.

With that statement you gave me though if I have 3 reels for a pubdateID with the same low number of images how do I return only 1 row that has the highest reelid???

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 11:44:00

oops - it's not using the reelid in the join.
Are your reelid's unique within pubdateid?
if so
select *
from tbl t
where reelid = (select top 1 reelid form tbl t2 where t2.pubdateid = t.pubdateid order by Icount, reelid desc)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bouwob
Starting Member

3 Posts

Posted - 2006-05-22 : 11:47:38

Perfect. Thanks a whole lot ;)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 11:51:29

Hi all,

Nigel - that seems to return 4 records, rather than 3 (3-1-1 gets included)

bouwob - is the code below roughly what you're after...?

--data
declare @t table (pubdateid int, reelid int, Icount int)
insert @t
          select 1,1,1
union all select 1,2,2
union all select 1,3,3
union all select 1,4,4
union all select 2,1,4
union all select 2,2,3
union all select 2,3,2
union all select 2,4,1
union all select 3,1,1
union all select 3,2,1
union all select 3,3,2
union all select 3,4,2

--calculation
select pubdateid, max(reelid) as reelid, Icount
from @t a
where Icount = (select min(Icount) from @t where pubdateid = a.pubdateid)
group by pubdateid, Icount
order by pubdateid

--results
pubdateid   reelid      Icount      
----------- ----------- ----------- 
1           1           1
2           4           1
3           2           1

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 11:53:29

Ah, I missed my slot

The above response was in reply to Nigel's first response. It looks like it's all sorted now

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources