help with selecting distinct

Posted on 2011-04-27
Last Modified: 2012-05-11
please refer to the sample data I have included.

I need to create a select statement that should work as follows:
for a given uid, if there is only a single record, then select that record.
for a given uid, if there are more than one record, and all the records have the same date, select one random record from that group.
for a given uid, if there are more than one record and the date are different, select only one with the max date. Also, this group, there could be more than one record with the "max date".

how can I accomplish this? thanks.
uid	name	date
1	name1	1/1/2011
2	name2	2/1/2011
2	name2	2/1/2011
3	name3	3/1/2011
3	name3	3/2/2011
3	name3	3/2/2011

Open in new window

Question by:IUAATech
    LVL 32

    Accepted Solution


    select uid, name, max(date)
    from Table1
    group by uid, name

    Author Comment

    nice. thanks so much.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now