I have a set of records with a repeating meeting# and ID, and I'm trying to grab the top 5 Meeting#'s.
Here's an example of some records:
Meeting# ID
1234 45942
1234 69745
1234 48441
1234 44784
1234 45878
1234 78478
1234 55698
1567 95777
1567 48748
1999 87547
1999 36487
1999 58745
1999 62478
1999 02787
1999 36987
and after getting the top 5 to look like this:
Meeting# ID
1234 45942
1234 69745
1234 48441
1234 44784
1234 45878
1567 95777
1567 48748
1999 87547
1999 36487
1999 58745
1999 62478
1999 02787
I don't want to use a cursor to get back the data. I tried using a while loop, and got a little stomped.
Thanks, Iris
Iris:
Give a look to the ROW_NUMBER() function in books online; also pay attention to the OVER(), PARTITION BY and ORDER BY clauses. I will se about getting you an example. The problem here is that there seems to be no logical order to the data.
Does your data have either (1) a CLUSTERED INDEX or (2) a PRIMARY KEY? I guess the real question is what do you mean by, "Top 5 meetings"?
|||
Hi Kent,
I had an example of that from http://www.extremeexperts.com/SQL/Articles/IterateTSQLResult.aspx, and got confused. Here's what I had:
Declare @.au_id Varchar(20)
SET ROWCOUNT 0
Select au_id, au_lname, au_fname into #Temp from authors
SET ROWCOUNT 1
Select @.au_id = au_id from #Temp
While @.@.rowcount <> 0
Begin
Select * from #Temp Where au_id = @.au_id
Delete from #Temp where au_id = @.au_id
Select @.au_id = au_id from #Temp
End
Set Rowcount 0
Drop table #Temp
I tried to change it to While @.@.rowcount <= 5 but my query just kept going and going and going....lol
Thanks, Iris
|||
Ok, for example, I was trying to grab the first 5 IDs for Meeting # 1234.
Iris
|||OK; another alternative might be to use a CROSS APPLY query.|||
Never heard of it. How does it get the top 5 IDs?
Iris
|||
Here is a way of getting the top 5 IF the top 5 are ordered by ID:
Code Snippet
--
-- Notice that this is NOT the same order
-- that you showed; if you want the TOP 5
-- from an unorderd list you might want to
-- use a temp table.
--
declare @.xample table
( Meeting# integer,
ID varchar(8)
)
insert into @.xample
select 1234, '45942' union all
select 1234, '69745' union all
select 1234, '48441' union all
select 1234, '44784' union all
select 1234, '45878' union all
select 1234, '78478' union all
select 1234, '55698' union all
select 1567, '95777' union all
select 1567, '48748' union all
select 1999, '87547' union all
select 1999, '36487' union all
select 1999, '58745' union all
select 1999, '62478' union all
select 1999, '02787' union all
select 1999, '36987'
--select * from @.xample
select Meeting#,
ID
from ( select Meeting#,
ID,
row_number() over
( partition by Meeting#
order by ID
) as Seq
from @.xample
) x
where Seq <= 5
order by Meeting#
/*
Meeting# ID
-- --
1234 44784
1234 45878
1234 45942
1234 48441
1234 55698
1567 48748
1567 95777
1999 02787
1999 36487
1999 36987
1999 58745
1999 62478
*/
Here is a way of getting the top 5 IF the top 5 are truely un-ordered:
Code Snippet
--
-- Use of a temp table allows the results
-- were initially specified.
--
declare @.xample table
( Meeting# integer,
ID varchar(8)
)
insert into @.xample
select 1234, '45942' union all
select 1234, '69745' union all
select 1234, '48441' union all
select 1234, '44784' union all
select 1234, '45878' union all
select 1234, '78478' union all
select 1234, '55698' union all
select 1567, '95777' union all
select 1567, '48748' union all
select 1999, '87547' union all
select 1999, '36487' union all
select 1999, '58745' union all
select 1999, '62478' union all
select 1999, '02787' union all
select 1999, '36987'
select Meeting#,
ID,
identity(int) as Seq
into #aTemp
from @.xample
select Meeting#,
ID
from ( select Meeting#,
ID,
row_number() over
( partition by Meeting#
order by Seq
) as Seq
from #aTemp
) x
where Seq <= 5
order by Meeting#
go
drop table #aTemp
go
/*
Meeting# ID
-- --
1234 45942
1234 69745
1234 48441
1234 44784
1234 45878
1567 95777
1567 48748
1999 87547
1999 36487
1999 58745
1999 62478
1999 02787
*/
Are either of these headed in the right direction?
|||
Thanks Kent. That makes logical sense.
Iris
|||
Code Snippet
createtable #meetings (meetingnbr int, id int)
insertinto #meetings
select 1234, 45942 union
select 1234, 69745 union
select 1234, 48441 union
select 1234, 44784 union
select 1234, 45878 union
select 1234, 78478 union
select 1234, 55698 union
select 1567, 95777 union
select 1567, 48748 union
select 1999, 87547 union
select 1999, 36487 union
select 1999, 58745 union
select 1999, 62478 union
select 1999, 02787 union
select 1999, 36987
select m.*
from #meetings m
innerjoin
(
selectdistinct meetingnbr from #meetings
)as ctl
on m.meetingnbr = ctl.meetingnbr
and m.id in
(
selecttop 5 id
from #meetings m2
where m2.meetingnbr = ctl.meetingnbr
orderby id
)
|||
You need not to have a temp table with IDENTITY. I just learnt that IDENTITY won't create a sequence number as expected.
The following query is more enough.
Code Snippet
Create Table #data (
[Meeting#] int ,
[ID] int
);
Insert Into #data Values('1234','45942');
Insert Into #data Values('1234','69745');
Insert Into #data Values('1234','48441');
Insert Into #data Values('1234','44784');
Insert Into #data Values('1234','45878');
Insert Into #data Values('1234','78478');
Insert Into #data Values('1234','55698');
Insert Into #data Values('1567','95777');
Insert Into #data Values('1567','48748');
Insert Into #data Values('1999','87547');
Insert Into #data Values('1999','36487');
Insert Into #data Values('1999','58745');
Insert Into #data Values('1999','62478');
Insert Into #data Values('1999','02787');
Insert Into #data Values('1999','36987');
;With CTE
as
(
Select
*
,Row_Number() Over (Partition By [Meeting#] Order By [Meeting#]) RowId
From
#data
)
Select
[Meeting#]
,[ID]
From
CTE
Where
RowID <=5
|||
Whenever I use row_number(), I get an error... 'row_number' is not a recognized function name. Any ideas why?
select meetingid, id
from ( select meetingid, id, row_number() over
( partition by meetingid
order by Seq )
as Seq
from #ids ) x
where Seq <= 5
order by meetingid
Iris
|||
Are you using SS 200 or 2005?
If you are using SS 2005, check the compatibility level of your db, and be sure it is 90. See sp_dbcmptlevel is BOL. If you are using SS 2000, then try:
select a.*
from dbo.t1 as a
where [ID] in (
select top 5 [ID]
from dbo.t1 as b
where b.Meeting# = a.Meeting#
order by [ID] ASC
)
go
AMB
|||
I am using SS 2000.
Thanks, Iris
|||
Thanks, this part worked for SS2000.
Iris
