tomrwilson
asked on
Select distinct problem
I have a table that is a set of responses to questions. Each record is stamped with a GUID to identify each record as being part of a set. So if I do
select distinct GUID from table1
I get a list of unique GUID's. But I want this ordered by the field Entered (entered date/time) I can't do this:
select distinct guid,entered from table1 order by entered
cause the sets are no longer distinct if I include 'Entered'. How can I get the query to return data that is also not distinct like the entered date for each 'GUID distinct' record it returns?
select distinct GUID from table1
I get a list of unique GUID's. But I want this ordered by the field Entered (entered date/time) I can't do this:
select distinct guid,entered from table1 order by entered
cause the sets are no longer distinct if I include 'Entered'. How can I get the query to return data that is also not distinct like the entered date for each 'GUID distinct' record it returns?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, I mistakenly left off the alias(es):
SELECT guid, MAX(entered) AS entered, MIN(someOtherColumn) AS someOtherColumn
FROM table1
GROUP BY guid
ORDER BY guid
SELECT guid, MAX(entered) AS entered, MIN(someOtherColumn) AS someOtherColumn
FROM table1
GROUP BY guid
ORDER BY guid
ASKER
That did it. Thanks!!
ASKER
SELECT guid, MAX(entered) from answers group by guid order by guid
I don't get an error but when I then try this:
Listbox1.AddItem rsAnswers!Entered
I get 'Item cannot be found in the collection...' I need to return both the distinct GUID and its entered date.