F M's Library tagged TSQL →  View Popular

I tried to do the same thing myself and finally this is how I worked it out :



SELECT

rowCode <---- (Some unique numeral key)

firstname,lastname,address1 <---- distinct columns

plandate,plandate2 <---- non distinct columns



FROM table



WHERE rowCode IN (SELECT Min(rowCode) FROM table GROUP BY firstname,lastname,address1)



ORDER by firstname



This will return unique values for your distinct columns and will the first (min) value of your non distinct columns .

---------------------------------------------------



Actually in your case, because your 'non distinct' column has a 'date' type, you can solve it more easily by using the following code :



SELECT Bank, Account, Note, MAX(PlanDate)

FROM table

GROUP BY Bank, Account, Note