473,327 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

filtering duplicate fields with unique rows

I have a query that outputs the following,

A | Name1 | Date1
A | Name2 | Date2
B | Name1 | Date1
B | Name2 | Date2

I would like to see,

A | Name1 | Date1
B | Name1 | Date1

In some cases, there could be more than 2 duplicates for the first
column field.

I would just like the first row of each duplicate field found in column
1.

Please advise as to the SQL Syntax I may be able to use to complete
this.

Thanks in advance.

Sep 5 '06 #1
2 1358
On 5 Sep 2006 08:39:45 -0700, go****@digiinsider.com wrote:
>I have a query that outputs the following,

A | Name1 | Date1
A | Name2 | Date2
B | Name1 | Date1
B | Name2 | Date2

I would like to see,

A | Name1 | Date1
B | Name1 | Date1

In some cases, there could be more than 2 duplicates for the first
column field.

I would just like the first row of each duplicate field found in column
1.

Please advise as to the SQL Syntax I may be able to use to complete
this.

Thanks in advance.
What you need is an aggregate query.

Select
Category,
First(NameField) AS NameField,
Max(DateField) AS DateField
From
MyTable
Group By
Category

HTH,
Randy
Sep 5 '06 #2
Thanks Randy. Found this on Microsoft's site,

http://office.microsoft.com/en-us/as...581033.aspx#13

Similar to what you are talking about.

Sep 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Alex Ayzin | last post by:
Hi, I have a problem that might be easy to solve(possibly, I've just overlooked an easy solution). Here we go: I have a dataset with 2 datatables in it. Now, I need to do the following: if...
1
by: Alex Satrapa | last post by:
I have a table from which I'm trying to extract certain information. For historical reasons, we archive every action on a particular thing ('thing' is identified, funnily enough, by 'id'). So the...
9
by: Catherine Jo Morgan | last post by:
Can I set it up so that a certain combination of fields can't contain the same entries, on another record? e.g. a combination of FirstName/LastName/address? Or FirstName/LastName/phone? Or...
8
by: Mark | last post by:
When my form goes to a new record, I have a procedure that copies the last record added to the form's underlying table into the form. The intent is that a series of new records may have the same...
5
by: hubmei75 | last post by:
Hello, I have a simple table containing adresses. A sample view of the table is id name city -------------------------------- 100 Meier New York 101 Meier Tokyo 110 ...
5
by: Manish | last post by:
The topic is related to MySQL database. Suppose a table "address" contains the following records ------------------------------------------------------- | name | address | phone |...
5
jamesd0142
by: jamesd0142 | last post by:
My manager and I where looking at some complex code to eliminate duplicate records in a database table. then it hit me how its done easily... so i thought i'd share it... In English:...
1
by: rdsandy | last post by:
Hi, I have a table Risk, a table Mitigation and a table RiskArchive. I am taking the RiskID, Criticality and MitigationPlan fields from Risk, and MitigationActionID from Mitigation and inserting...
4
by: Thomas Arthur Seidel | last post by:
Hello to all, I have a small or big problem with a customer data base, where during a change of system we might have created duplicate records. This should be easy to find, you might think, but,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.