[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


help with selecting distinct

Posted on 2011-04-27
Medium Priority
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

Ephraim Wangoya earned 2000 total points
ID: 35479730

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

Author Comment

ID: 35479764
nice. thanks so much.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

867 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