473,569 Members | 2,611 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL - SELECT DISTINCT

Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin
Nov 12 '05 #1
9 10848
Maybe you're out with a Totals query; you can group by Ref (in your
sample) and choose e.g. First for other fields. Sum(date) doesn't really
make sense does it?

Kelvin wrote:
Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin


--
Bas Cost Budde

Nov 12 '05 #2

What a melon scratcher!

The way I would tackle that problem would be to do a query for each of the fields, using the "distinct" parameter.
qryREF_field = "select distinct REF from tblWhatever"
qryNAME_Field = "select distinct NAME from tblWhatever"
etc...

Then would create another query using the table, and each of the separate queries for the fields.
Join the appropriate fields from the queries to the fields on the main table. The default join is what you want.
- So long as those joins are in place.

Then add in all of the fields from tblwhatever, and hopefully should work!? (no time to try it at the moment I'm sorry, but let me know how it goes and if it doesn't work will have a closer look tomorrow for you!)

Regards,

Fraser.

ke************* *@dsl.pipex.com (Kelvin) wrote:
Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin


Nov 12 '05 #3
You're right Sum(date) isn't really any good to me but I understand your
train of thought. What it is I'm after doing is this. I have a
database of empolyees, everytime one of these employees leaves I appded
that employees details to a leavers table with additional information
such as the date they left and and comments pertaining to that person.
Then the original record is then removed from the current employees
table.

E.G.

tblCurrentEmps -----------> tblLeavers
xxOrigRemovedxx xxDate&Comments Addedxx

Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4
Try something like this:

select tblwhatever.*
from tblwhatever
where (((tblwhatever) In (select [Ref] FROM [tblwhatever] as tmp group by
[Ref] having Count(*)<2 )))
order by tblwhatever.Ref ;

That would work for eliminating results where 1 field contains duplicate
info. If you wanted to include this criteria for every field then include
more sub-queries.
e.g.:

select tblwhatever.*
from tblwhatever
where (((tblwhatever. Ref) in (select [Ref] from [tblwhatever] as tmp group
by [Ref] having Count(*)<2 )) and ((tblwhatever.[Name]) in (select [name]
from [tblwhatever] as tmp group by [name] having count(*)<2 )))
order by tblwhatever.ref ;
Mark
"Kelvin" <ke************ **@dsl.pipex.co m> wrote in message
news:17******** *************** *@posting.googl e.com...
Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin

Nov 12 '05 #5
Maybe you should not use the employee ID as primary key (I guess you
don't already)

Take a two-step approach. Create a query that retrieves the oldest
employee IDs with their true PK, presumably some autonumber (I must
review SQL books on how exactly, maybe someone else here can show how.
Grouping on empID alone will not do the trick; first(date) will show the
first date, but how to retrieve the PK?)
On this query, create another that retrieves all other information about
the history.

Kelvin Middleton wrote:
Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


--
Bas Cost Budde

Nov 12 '05 #6
<fr***@clear.ne t.nz> wrote in message news:<40******* *@clear.net.nz> ...
What a melon scratcher!

The way I would tackle that problem would be to do a query for each of the fields, using the "distinct" parameter.
qryREF_field = "select distinct REF from tblWhatever"
qryNAME_Field = "select distinct NAME from tblWhatever"
etc...

Then would create another query using the table, and each of the separate queries for the fields.
Join the appropriate fields from the queries to the fields on the main table. The default join is what you want.
- So long as those joins are in place.

Then add in all of the fields from tblwhatever, and hopefully should work!? (no time to try it at the moment I'm sorry, but let me know how it goes and if it doesn't work will have a closer look tomorrow for you!)

Regards,

Fraser.

ke************* *@dsl.pipex.com (Kelvin) wrote:
Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin


Its a good idea but how would I maintain the relations ships between
ref and name if I'm querying them individually.

In my example above a "SELECT DISTINCT Ref FROM tblWhatever" would
return:-

Ref
AAA
BBB

And a "SELECT DISTINCT Name FROM tblWhatever" would return:-

Name
Joe
Ken
Len

So they don't match up, and don't forget I need to query them by order
of 'date'...just to make it even harder :-)

I've had an idea of creating the SQL to join back onto the same table
in a subquery and compare the REF field like that but although I've
heard of this before I've never actually joined a table back onto
itself before. Could this be a possibility?

I appreciate all this help folks, cheers.

Kelvin
Nov 12 '05 #7
Hi,

Take all of this with a grain of salt; but I wouldn't remove employees
from the original table at all. I would merely have a field indicating
that they were or were not a current employee (in my apps, it is
usually just a "deleted?" field) and have a related table of leavers
that has the termination information. That way you never have to deal
with duplicate or incorrect keys, etc.

JimA
Kelvin Middleton <ke************ **@dsl.pipex.co m> wrote in message news:<40******* *************** *@news.frii.net >...
You're right Sum(date) isn't really any good to me but I understand your
train of thought. What it is I'm after doing is this. I have a
database of empolyees, everytime one of these employees leaves I appded
that employees details to a leavers table with additional information
such as the date they left and and comments pertaining to that person.
Then the original record is then removed from the current employees
table.

E.G.

tblCurrentEmps -----------> tblLeavers
xxOrigRemovedxx xxDate&Comments Addedxx

Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #8
Cheers Jim but I can't leave them in the same table you see each
employee has a unique ID which is also what they use to access several
of our IT systems. As the system is quite old there is only a finite
number of these unique ID's and as such we re-allocate old ones from
leavers to new starters. So as I said these ID's are unique and thus
they are the Primary Key for each record so I have to remove the leavers
into another table when they go.

Thanks as well to Bas and Mark gunna try your ideas out soon, i'll let
ya know how I've got on.

Kelvin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #9

Not trying to achieve much... ...Perhaps a better method might have been to keep unique ID's? For example, at my last job every employee had an employee number that was unique to them. It was used for payroll, etc. If you have something like that it might be a good idea to capture and use because it is truly unique.

However, that's not the case, and not the answer to your question, so here goes...

two queries.
First query gets the data from tblLeavers.
The second query should be an "unmatched records" query (You'll find there is a wizard for this in access). This query should filter out all of the records that are in the tblLeavers AND tblCurrentEmp tables.

You will then need to combine these queries into one... ...to do this you will need to ensure both of your two new queries have the same fields, and in the same order. Once this is the case, go into the SQL view of a new query and type along the lines of:

"SELECT *
FROM qrytblLeavers

UNION

SELECT *
FROM qrytblCurrentEm p_Unmatched;"

Then try that. That will take all of the records from tblLeavers, and only those records from tblCurrentEmp that don't have a matching ID in tblLeavers.

Hope that helps,

Fraser.

Kelvin Middleton <ke************ **@dsl.pipex.co m> wrote:
You're right Sum(date) isn't really any good to me but I understand your
train of thought. What it is I'm after doing is this. I have a
database of empolyees, everytime one of these employees leaves I appded
that employees details to a leavers table with additional information
such as the date they left and and comments pertaining to that person.
Then the original record is then removed from the current employees
table.

E.G.

tblCurrentEm ps -----------> tblLeavers
xxOrigRemovedx x xxDate&Comments Addedxx

Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 12 '05 #10

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

Similar topics

5
11679
by: Martin Feuersteiner | last post by:
Dear Group I'm having trouble with the clause below. I would like to select only records with a distinct TransactionDate but somehow it still lists duplicates. I need to select the TransactionDate once as smalldatetime and once as varchar as I'm populating a drop-down with Text/Value pairs. So I can't just use 'SELECT DISTINCT...
3
10537
by: blue | last post by:
I'm trying to order a varchar column first numerically, and second alphanumerically using the following SQL: SELECT distinct doc_number FROM doc_line WHERE product_id = 'WD' AND doc_type = 'O' ORDER BY CASE WHEN IsNumeric(doc_number) = 1 THEN CONVERT(FLOAT, doc_number) ELSE 999999999 END,
2
12537
by: mfyahya | last post by:
I have two tables, both containing an 'authors' column. Is there a way to get a unique list of authors from the two tables? I tried SELECT DISTINCT `authors` from `table1`, `table2`; but I got an "Column 'authors' in field list is ambiguous" error. Is there also a query to return only the count of distinct authors from the two tables? ...
3
6436
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I COULD be wrong... :) I've tried the access group...twice...and all I get is "Access doesn't like ".", which I know, or that my query names are too...
6
5541
by: John M | last post by:
Hi, The line below is used to feed a combobox. (It is from a database which is used to log pupil behaviour!) The 'incidents' table contains a list of students who have been involved in incidents. Some may appear several times, hence the Distinct. However, thelist generated should still be sorted by Surname. When I add Order by I'm told...
18
3040
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are only returning one record. She states that with disctinct the query stops as soon as it finds a matching record. Both of us are relative novices...
3
5642
by: orekinbck | last post by:
Hi There Our test database has duplicate data: COMPANYID COMPANYNAME 1 Grupple Group 2 Grupple Group 5 Grupple Group 3 Yada Inc 4 Yada Inc
6
13546
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER BY;2 Message: An expression in the ORDER BY clause in the following position, or starting with "CASE..."...
5
6888
by: Daniel Wetzler | last post by:
Dear MSSQL experts, I use MSSQL 2000 and encountered a strange problem wqhile I tried to use a select into statement . If I perform the command command below I get only one dataset which has the described properties. If I use the same statement in a select into statement (see the second select) I get several datasets with the described...
0
7700
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7614
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
5513
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5219
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3642
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2114
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1221
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
938
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.