July 16, 2008 at 12:24 pm
The following query:
SELECT a.ProgramID, a.Term, b.shortname FROM dbo.tblProgram a JOIN tblProgramType b ON a.ProgramTypeID = b.ProgramTypeID ORDER BY a.Term, b.shortname DESC
returns data like this:
ProgramIDTermshortname
175AugustTeach
214AugustTeach
483AugustTeach
174AugustIFDS
222AugustIFDS
484FallStudy
567FallStudy
621 Fall Teach
455FallTeach
The combination Term-shortname defines a group. Is there a way to select only one row from each group in a view?
It doesn't matter which row in the group just one from each.
Thanks!
Jonathan
July 16, 2008 at 12:42 pm
If it does not matter which one you see, then why not just exclude ProgramID from the results altogether?
SELECT DISTINCT
a.Term, b.shortname
FROM dbo.tblProgram a JOIN tblProgramType b
ON a.ProgramTypeID = b.ProgramTypeID
ORDER BY a.Term, b.shortname DESC
If you do need to see one, then maybe this will work for you...
SELECT
MIN(a.ProgramID),a.Term, b.shortname
FROM dbo.tblProgram a JOIN tblProgramType b
ON a.ProgramTypeID = b.ProgramTypeID
GROUP BY a.term,b.shortname
ORDER BY a.Term, b.shortname DESC
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 16, 2008 at 12:52 pm
Thanks OldHand; the second one is exactly what I need! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply