Selecting one record per individual

[aggieval]

I am trying to write a query that will select one record per individual where there is a possiblity that there are more. The problem is that I need it to select multiple fields from the one table and these fields have to stay together (Cert Code, Cert #, Cert Date). In Access I had this running to find the first and this worked fine, but it does not appear that I have the same option in SQL.

Any advice would be greatly appreaciated.

Tracy

 

[ClaireHCM]

Can you give some sample data of the table?

 

[aggieval]

I am linking based on an Individual Course ID that might be 123. The table that is finding the prerequisites for the course might find multiple records (the prereq might be 001.1 or 004.4) The query as is will find:

IndCourseID, Cert Code, Cert #, Cert Date
123, 001.1, 12345, 1/1/03
123, 004.4, 55555, 9/10/02

Basically, I just want one of these records returned, but the Cert Code, Cert # and Cert Date have to stay together.

Again, Thanks for any help,
Tracy

 

[oricteropo]

Will Select Top 1 * from TableX work?

 

[aggieval]

I don't think so because I need the Top 1 for each IndCourseID, but I could very well be wrong.

 

[ClaireHCM]

If you dont mind to create another table for select statement,here is one way

select identity(int,1,1) as id,* into t5 from t4 order by indcourseid,cert_code desc
go
select indcourseid,cert_code,cert#,cert_date from t5 where id in (select max(id) from t5 group by indcourseid)
go