Have a table that is something like this:
table: eventRecords
cols:
timestamp – datetime
eventid – varchar
user – varchar
filepath – varchar
I want to select one record for each unique “eventid”, and I need for the data in all columns to be returned – not just eventid. The problem is:
“Select distinct eventid, timestamp, user, filepath from eventRecords” returns duplicate reports for eventid. Yes, I know this is the expected functionality for SQL. I’ve tried numerous variations to just return ONE record for each eventid, including:
Declare @mycount varchar
Set @mycount=(SELECT COUNT (DISTINCT [eventid])
FROM eventRecords)
And then plugging in @mycount as:
SELECT DISTINCT top (@mycount) eventid, timestamp, user, filepath from eventRecords
This brings back the right number of records, but there are duplicates of eventid still…
What step am I missing?