Help using TOP n
-
I've been struggling with this one for a while. Guess it's easy when you know how??
I am running a query across two views.
View A contains People records
View B contains diary entries for those people records.
There are multiple diary entries on View B for each of the View A Records
I want to run a query giving the name of the people from View A and just the first diary entry against each person from ViewB . Tried TOP 1 but seem to be having syntax problems.
HELLLLLLP please!
-
select sysobjects.name,max(syscolumns.name) from sysobjects,syscolumns where sysobjects.id=syscolumns.id group by sysobjects.name
this finds the max value, not the TOP value. might be applicable to you though
MVDBA
-
this works using the top method
i have used a query to pick the top column from each table in the database - replace with your own table names and foreign keys
create table #mytemptable ..... (add in table design here)
declare @sysid int
declare curs1 cursor for select id from sysobjects
open curs1
fetch next from curs1 into @sysid
while @@fetch_status=0
begin
insert into #mytemptable select @sysid,(select top 1 name from syscolumns where id=@sysid)
fetch next from curs1 into @sysid
end
close curs1
deallocate curs1
select * from #mytemptable
drop table #mytemptable
MVDBA
-
This will give you the 1st entry made in the diary per person in the People table. You will need to modify the VIEW to use your table fields
CREATE VIEW vwFirstDiaryEntry
AS
SELECT *
FROM People PE
INNER JOIN (SELECT MIN(DiaryDate) MinDiaryDate, PeopleID
FROM Diary
GROUP BY PeopleID) MN
ON PE.[ID] = MN.PeopleID
INNER JOIN Diary DY ON MN.PeopleID = DY.PeopleID
AND MN.MinDiaryDate = DY.DiaryDate
Good Hunting!AJ Ahrens
webmaster@kritter.net -
You can also use the TOP 1 syntax in the (select...) as below : use Northwind
select systab.name as Tables, syscol.name as Champs
from syscolumns syscol, sysobjects systab
where syscol.id = systab.id
and systab.xtype = 'U'
and syscol.name = (select TOP 1 name from syscolumns firstcol where firstcol.id = syscol.id order by colid)
...this will give the first column for each table define in the Northwind database. The point is that you can "order by" the subquery with the relevant criteria without the need of using a min or other aggregate function... (which is not always the rigth method)
-
Not sure why people are offering examples with sysobjects etc. What you want here is just a correlated subquery:
Assuming View A has columns PersonName, PersonID
View B has columns PersonID, DiaryEntry, DiaryEntryDate
then just
select PersonName, (select top 1 DiaryEntry from B where B.PersonID=A.PersonID order by DiaryEntryDate desc) as LatestEntry from A
The subquery automatically joins to the diary entries for a given person on a row by row basis (this is the 'correlated' bit). Selecting top 1 while ordering by date desc will give the latest entry each time.
-
Thank you everyone! After a few false starts I finally got there.
The last two postings from Jean-Pierre and AKM really helped to make it clear where I was going wrong.
So glad I found this site, everyone is always helpful.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply