Removing Duplicates in a Query 1

[skurkal]

I have a data set as follows:

A 11:50 $20
A 12:30 $40
A 10:30 $25
B 9:30 $35
B 9:30 $35
C 6:45 $45

The result I want is :

A 12:30 $40
B 9:30 $35
C 6:45 $45

Basically I want one row for the highest recorded time, ordered by column1 ascending.
I think I need an inner query with a group by, and an outer query with an ordered by. But whatever I am trying does not work. HELP!

 

[gmmastros]

Select  Distinct 
	MyTable.ColA,
	MyTable.ColB,
	MyTable.ColC
From	MyTable
	Inner Join (
		Select 	ColA, 
			Max(Colb) As MaxTime
		From 	MyTable 
		Group By ColA
		   ) A On MyTable.ColA = A.ColA 
			And MyTable.ColB = a.MaxTime

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[skurkal]

Thanks a lot! This worked perfectly. The solution was dead ON!

Which book would you recommend for becoming more proficient in writing SQL queries?

 

[gmmastros]

Books -- Schmooks.

I haven't had a lot of luck learning SQL Server from books. Instead, I've learned a lot by reading the questions and the responses in this forum.

Of course, if you are really interested in books, some others in this forum will probably have some good suggetions.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom