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
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.