Finding duplicate entries
-
Ivelisse you are in good hands sushila will help you solve your problems.
Sushila I would love to stay and play but I'm expecting a flurry of e-mails from overseas starting in about an hour and I need to finish this report. If I had just been born rich instead of goodlooking I would not have to work.
Enjoy your evening
Mike
-
LOL...
Mike - if you have some time c'mon over to the other post "sql query again - complex" in this forum and see if you can shed some light...
oops - too late i guess!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks.
I gave it a try but, it quite does not return everything I need. I need for it to return all the rows with the same duplicate.
-
ivelisse - could you please post what you ARE getting and what you WANT to get ?! thx!
**ASCII stupid question, get a stupid ANSI !!!** -
if object_id('tempdb..Test') >0
DROP TABLE Test
CREATE TABLE Test
(
"Date" datetime,
"Unit" varchar(10),
Company_Name varchar(20)
)
insert into Test values('4/2/05','bu002','ABC Company')
insert into Test values('11/30/04','bu002','ABC Company')
insert into Test values('4/2/05','bu002','ABC Company')
insert into Test values('5/12/05','bu002','ABC Company')
insert into Test values('11/3/05','bu002','ABC Company')
SELECT DISTINCT A.Unit,A.Company_Name
FROM Test A
-- Returns Bu002 ABC Company
HTH Mike
just could not stay away found an open window and had to take a lool
-
I was there earlier. Very distinct programming style.
If I get involved in that one I will never get anything done. Besides it is much more fun to help someone like Ivelisse who is working hard to understand what is going on.
Mike
-
I'm not very good at queries. I appreciate your patience.
When I run this:
SELECT DISTINCT COMPANY
FROM Test
It returns only one hit, shouldn't it be returning more than one.
-
ivelisse - there is only one distinct company you have - abc company - if you want all the columns, you need to do a "select distinct * from test"....
**ASCII stupid question, get a stupid ANSI !!!** -
That's much better. I was able to order by Company. How can I also order by date and unit?
-
you "order by" exactly as it sounds...
select distinct * from test
order by company, date, unit...(or whatever order you want to specify!)
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks I was able to figure it out. One more question, I made more duplicate entries on purpose. When I run the select * from test it only returns 7 and there are 11 entries in the table.
-
ivelisse - maybe you still had the "distinct" in your query without realizing it ?!
select * from test - should give you all 11 rows.
select distinct * from test - should give you only those rows that have a unique combination of date, unit and company name.
**ASCII stupid question, get a stupid ANSI !!!** -
You were right, forgot to remove it. Thank you so much for all your help.
-
glad it all worked out!
"see" you around Mike!
**ASCII stupid question, get a stupid ANSI !!!** -
Check and see how many rows are dublicates.
if object_id('tempdb..Test') >0
DROP TABLE Test
CREATE TABLE Test
(
"Date" datetime,
"Unit" varchar(10),
Company_Name varchar(20)
)
insert into Test values('4/2/05','bu002','ABC Company')--this is a dup
insert into Test values('4/2/05','bu002','ABC Company')--with this
insert into Test values('4/2/05','bu002','ABC Company')-- and this
insert into Test values('5/12/05','bu002','ABC Company')
insert into Test values('11/3/05','bu002','ABC Company')
insert into Test values('11/30/05','bu003','DEF Company')
insert into Test values('11/3/05','bu003','HIJ Company')
SELECT Distinct *
FROM Test A
order by a."date",a.unit,A.Company_Name
--will only return 5 rows
HTH Mike
BTW this time I really am going back to work.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply