May 28, 2009 at 8:53 am
I need to show distinct records based only on the Course_Number column.
This is what I have so far.
SELECT CourseUniqueID, Course_Number, Course_Title,
course_Description_short
FROM course
WHERE course_end > getdate()
ORDER BY course_Number
For example from the following recordset, only the 1st should show since they both have 100 as the Course_Number
133100Course1Description1
101100Course2 Description2
Not sure how to do this.
Thank you,
Norbert
May 28, 2009 at 8:58 am
Having you tried looking into the ROW_NUMBER() function and or the GROUP BY function for this type of problem.
If you need more help just shout
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 28, 2009 at 9:00 am
Heard about ROW_NUMBER() function with PARTITION BY clause? That's what would do the job here. Here is the sample code from BOL (works on AdventureWorks)
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate) AS RowNumber, *
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber = 1;
--Ramesh
May 28, 2009 at 11:04 am
Ramesh and Christopher Stobbs:
Thank you very much. This is exactly the solution I was looking for!
Norbert
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply