Duplicates Despite Using UNION
Apr 30, 2008
Hello,
I am doing a UNION (not UNION ALL) of two tables and two columns. The first column is AcctCode VARCHAR(16), and the second column is Revenue FLOAT.
I am getting two rows back, using UNION, where the values in both columns are the same. Basically, it looks like this:
AcctCode Revenue
AM247 300.64
AM247 300.64
There are trailing spaces after the AcctCode, and I have tried RTRIM.
The following is the query I am using:
Select
RTRIM(AcctCode)AS Acctcode,
SUM(ISNULL(ScFee,0)) as Revenue
From cdnbwfin1.txnRptg.dbo.dailySummary
where TxnDate = '4/22/2008'
group by Acctcode
union
Select
RTRIM(AcctCode) As AcctCode,
SUM(ISNULL(ScFee,0)) as Revenue
From bwdbfin1.txnrptg.dbo.tbl_dailySummary
where TxnDate = '4/22/2008'
group by Acctcode
order by acctcode
Why would I get this duplicate if I'm using UNION?
Thank you for your help!
cdun2
View 9 Replies
ADVERTISEMENT
Mar 12, 2008
Hi -
I am a newbie to t-sql and have an issue that I am not sure is me or sql
I want to merge several tables from several databases.
I have created a union statement:
create view myViewas
select *, ‘1’ as compId, ‘AAA’ as SiteID from ClientAAA.dbo.stats
UNION
(select *, ‘2’ as compID, ‘ABC’ as SiteID from clientABC.dbo.stats
UNION
(select *, ‘3’ as compID, ‘ABD’ as SiteID from clientABD.dbo.stats
UNION
(select *, ‘4’ as compID, ‘ABF’ as SiteID from clientABF.dbo.stats
UNION
(select *, ‘5’ as compID, ‘AGG’ as SiteID from clientAGG.dbo.stats
))))
Its ok till the last statement then they repeat from the 4th line stats.
Any help would be great
View 2 Replies
View Related
Jan 7, 2012
This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.
However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed. For example, if I wanted to remove user "sam@mail.com"
The table results displayed would be:
Row 1:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
NewParamValue: jack@mail.com;frank@mail.com
Row 2:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;john@mail.com
NewParamValue: jack@mail.com;;frank@mail.com
Ideally, it should only display each row once, and not have the semicolon error. It seems to be a union error, because when I comment out the First and second union statements, it runs fine.
-- Delete email address from a.Batch
IF(@EmailAddress IS NOT NULL)
BEGIN
IF(LEN(@EmailAddress) > 0)
BEGIN
IF(@ShowOnly = 1)
[Code] ......
View 2 Replies
View Related
Sep 9, 2004
Hello All,
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)
I am trying to run another query on my first query results so I don't mess my first query up. Here it is:
SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";
Please Help! Thank you in advance.
--------------------
5 minutes away from pulling my last one!
BaldNAskewed
View 7 Replies
View Related
Oct 22, 2014
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,
[code]....
View 9 Replies
View Related
Jun 12, 2014
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
View 4 Replies
View Related
Jan 26, 2015
Is there a query or a way to convert duplicates value in a column to non duplicates.
View 14 Replies
View Related
Apr 29, 2008
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
View 3 Replies
View Related
Nov 6, 2006
Hi all,
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
Any idea why this could happen ?
View 18 Replies
View Related
Mar 2, 2006
I need some help.
I have created a database that looks like the following:
FirstName Table link to Main Table.
I have created a Stored procedure that looks like this:
Create procedure dbo.StoredProcedure
(
@FirstName varchar(20)
)
Declare FirstNameID int
Insert Into Main Table
(
FirstName
)
Values
(
@FirstName
)
Select @FirstNameID = Scope_Identity()
How could I redesign this to check if a value exists and if it exists then simply use that value instead of creating a new duplicate value?
Thanks!!
View 2 Replies
View Related
Jun 27, 2001
I have a dilema..... I have a databas eof about 60,000 users and i need to get rid of those users where there is a duplicate email address. I have written an asp utilty that works but is far too taxing on our little server and i thinkk itwill kill it. what it does is for each email address it compares it against all the others.... so for each address it checks against 60,000 other records 60,000 times.... you know what i mean. its pretty phucked.... i tested it on just one record and took about 5mins.
anyway ive been trying to do it in SQL with no luck
here is a simplified version of my dilema
*****
NAMEEMAILAGE
tomtom@mail23
tombomb tom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
marycool@mail89
thomas tom@mail13
richrich@mail65
richdick@mail65
tomtom@mail23
tomtom@mail23
so what i want to do is cut it down so there are no duplicate email addresses. I want the table looking like this:
*****
NAMEEMAILAGE
tomtom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
richrich@mail65
richdick@mail65
Can you think of a way to do this?
i tried the following but it gets rid of duplicates of other fields which is not what i want to do
CREATE table distinct_records
SELECT name, Email, Age
FROM duplicate_records
GROUP BY name, Email, Age;
any ideas guys?
thanks
tom.harrow@netpoll.net
View 1 Replies
View Related
Nov 7, 2005
i've imported a number of spreadsheets into a database
the schema is
directoryid firstname, lastname, extension
703168 Andrew Lim 78094
703154 Joseph Egan 78888
704548 Andrew Lim 78094
I realized the spreadsheet had a large number of duplicates. How can i delete the duplicates ?
like deleting directoryid 703168 only.
View 1 Replies
View Related
Nov 9, 2005
i'm trying to get duplicates out of the my database
SELECT COUNT(*) AS Amount,
Firstname,
surname,
Internalextension
FROM iac.dbo.sf_profil
GROUP BY FirstName, surname, internalextension
HAVING COUNT(*) > 1 order by firstname, surname
How do i alter the query just retrieve records which have firstname and lastname which are similar but different extension numbers ?
View 2 Replies
View Related
May 8, 2008
Hi,
This is the query which shows me the duplicates
Some of the records have more than one records
I would like to know how to delete the extra records so that I will end up with one record per row.
select
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last,
Count(*) as 'count'
from
tblPricesClean
group by
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last
having count(*) > 1
order by
count desc
View 4 Replies
View Related
Jun 13, 2008
Hi,
Is there a way to find duplicates in one field? For example my query has person_nbr and for each person_nbr on one day they could have used multiple payer_names. I want to be able to count each person_nbr one time but also I want to group by description(which is the name of the provider) and by payer name to see how many person's that the provider seen with each payer. My problem is that if the person had more than one payer they are counted twice. Is there some type of aggregate function to use the first payer in the list??
With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
join person_payer d on tmp.person_id = d.person_id
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp
Thanks in Advance!
Sherri
View 4 Replies
View Related
May 10, 2006
Hi,
I'll see if I can explain this clearly. The query below
selects rows from the "hdr_ctl_nbr_status" table if the value in the field "tcn" from that table is found in the table "temp_tcn". I want all fields from the "hdr_ctl_nbr_status" table to be selected BUT only one row. In other words for a tcn with a value "12345678" there are 10 rows returned from the hdr_ctl_nbr_status table, I want only 1. Is there a way I can use SELECT DISTINCT to do this ? I know this usually functions on one or more fields but I want the DISTINCT to be on tcn only BUT return all fields in the query.
Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK)
where h.tcn in (select tcn from temp_tcn)
Thanks, sorry if this is too confusing. Jeff
View 14 Replies
View Related
Mar 10, 2008
Hi, All,
I have two columns of int data in the a table, as my example data shows below.
I want my data returned to be something like those in #test3, but my question is this, how can I do it without using #test2 and #test3?
By the way, the business requirement doesn't care it's min/max or any ID when one side has duplicated values.
Thanks!
Use tempdb
Go
if object_ID ('#test') is not null drop table #test
create table #test (col1 int, col2 int)
insert into #test
Select 123, 222 union
Select 124, 222 union
Select 125, 222 union
Select 111, 223 union
Select 111, 224
if object_ID ('#test2') is not null drop table #test2
create table #test2 (col1 int, col2 int)
Insert into #test2
Select distinct col1, min(col2) from #test group by col1
if object_ID ('#test3') is not null drop table #test3
create table #test3 (col1 int, col2 int)
Insert into #test3
Select min(col1), col2 from #test2 group by col2
Select * from #test3
View 2 Replies
View Related
Jul 28, 2005
I am attempting to execute the Stored Procedure at the foot of thismessage. The Stored Procedure runs correctly about 1550 times, butreceive the following error three times:Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,Line 68Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I've done some digging, and the error message is moderatelyself-explanatory.The problem is that there is no Line 68 in the Stored Procedure. It'sthe comment line:-- Need to find out how many hours the employee is scheduled etc.Also, there are no duplicate records in the Employee table nor theWeeklyProfile table. At least I assume so - if the following SQL todetect duplicates is correct!SELECT E.*FROMEmployee Ejoin(select EmployeeIDfromEmployeeGroup by EmployeeIDhaving count(*) > 1) as E2On(E.EmployeeID = E2.EmployeeID)SELECTW.*FROMWeekProfile Wjoin(SelectWeekProfileIDFROMWeekProfileGROUP BYEmployeeID, MondayHours, WeekProfileIDHAVING COUNT(*) > 1) AS W2ONW.WeekProfileID = W2.WeekProfileIDNOTE: In the second statement, I have tried for MondayHours thruFridayHours.Anyone got any ideas? The TableDefs are set up in this thread:<http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/fff4ef21e9964ab8/f5ce136923ebffc3?q=teddysnips&rnum=1&hl=en#f5ce136923ebffc3>The Stored Procedure that causes the error is here:--************************************************** ***********CREATE PROCEDURE BackFillNetworkHoursASDECLARE @EmployeeID intDECLARE @TimesheetDate DateTimeDECLARE @NumMinutes intDECLARE @NetworkCode int-- Get the WorkID corresponding to Project Code 2002SELECT@NetworkCode = WorkIDFROM[Work]WHERE(WorkCode = '2002')-- Open a cursor on a SELECT for all Network Support Employees whereany single workday comprises fewer than 7.5 hoursDECLARE TooFewHours CURSOR FORSELECTEmployeeID,CONVERT(CHAR(8), Start, 112) AS TimesheetDate,SUM(NumMins) AS TotalMinsFROM(SELECTTI.EmployeeID,W.WorkCode,TI.Start AS Start,SUM(TI.DurationMins) AS NumMinsFROMTimesheetItem TI LEFT JOIN[Work] W ON TI.WorkID = W.WorkIDWHERE EXISTS(SELECT*FROMEmployee EWHERE((TI.EmployeeID = E.EmployeeID) AND(E.DepartmentID = 2)))GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS xGROUP BYEmployeeID,CONVERT(char(8), Start, 112)HAVINGSUM(NumMins) < 450ORDER BYEmployeeID,CONVERT(CHAR(8), Start, 112)-- Get the EmployeeID, Date and Number of Minutes from the cursorOPEN TooFewHoursFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesWHILE (@@FETCH_STATUS=0)BEGINDECLARE @NewWorkTime datetimeDECLARE @TimesheetString varchar(50)DECLARE @Duration intDECLARE @RequiredDuration int-- Set the correct date to 08:30 - by default the cast from thecursor's select statement is middaySET @TimesheetString = @TimesheetDate + ' 08:30'SET @NewWorkTime = CAST(@TimesheetString AS Datetime)-- Need to find out how many hours the employee is scheduled to workthat day.SET @RequiredDuration = CASE (DATEPART(dw, @NewWorkTime))WHEN 1 THEN(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 2 THEN(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 3 THEN(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 4 THEN(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 5 THEN(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 6 THEN(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 7 THEN(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))ENDIF @NumMinutes < @RequiredDurationBEGIN-- Set the Start for the dummy work block to 08:30 + the number ofminutes the employee has already worked that daySET @NewWorkTime = DateAdd(minute, @NumMinutes, @NewWorkTime)-- Set the duration for the dummy work block to be required durationless the amount they've already workedSET @Duration = @RequiredDuration - @NumMinutes-- Now we have the correct data - insert into table.INSERT INTO TimesheetItem(EmployeeID,Start,DurationMins,WorkID)VALUES(@EmployeeID,@NewWorkTime,@Duration,@NetworkCode)ENDFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesENDCLOSE TooFewHoursDEALLOCATE TooFewHoursGO--************************************************** ***********ThanksEdward
View 3 Replies
View Related
Jul 20, 2005
I have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4,COL5, COL6).... I need to be able to select all columns/rows whereCOL3, COL4, and COL5 are unique....I have tried using DISTINCT and GROUP BY, but both will only allow meto access columns COL3, COL4, and COL5..... i need access to allcolumns...I just want to get rid of duplicate rows (duplicates ofCOL3, COL4, and COL5)...Thanks in advance.Joe
View 1 Replies
View Related
Apr 23, 2008
Anyone know how to sum duplicated values so it only sums the distinct values?
View 3 Replies
View Related
May 21, 2008
I have the following data.
CustID RoleID RelatID StartDate EndDate
20 RIX AGQ 5/05/2007 31/12/9999
20 RIX AGR 10/06/2007 31/12/9999
18 LRS AGQ 3/09/2004 31/12/9999
22 SRT AGP 5/03/2007 31/12/9999
22 SRT AGP 10/03/2007 31/12/9999
I wish to SELECT only the records that have duplicates. So, in the above data example, RoleID of LRS should be excluded.
How to do?
View 11 Replies
View Related
Feb 12, 2007
Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc
Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.
I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...
View 16 Replies
View Related
Feb 25, 2004
I have these two tables and I cant prevent duplicates.
SEARCH
Item
ItemID
Info
CATEGORYDATA
CategoryID
ItemID
SELECT DISTINCT SEARCH.ItemId, SEARCH.Item, CATEGORYDATA.CategoryId
FROM SEARCH
INNER JOIN CATEGORYDATA
ON SEARCH.ItemID = CATEGORYDATA.ItemID
And I get something like:ItemID Item CategoryID
1 item1 1
3 item3 1
1 item1 2 <---duplicate
1 item1 3 <---duplicate
2 item2 3
4 item4 3
Thanks in advance
View 4 Replies
View Related
Feb 15, 2002
Hello!
Just looking for advise on dealing with duplicates in database.
I have a contact table that have a bunch of duplicated customer records.
My goal is to combine all duplicated records into one record.
This involves couple tables:contact,contact history ,calendar.
All tables related by common column "accountno".
What would be the best approach for this?
Thank you,
Lena
View 2 Replies
View Related
Apr 17, 2001
Hi,
does anyone out there know how to delete dulicate records in a table? I've looked in BOL and I can't seem to find it. Please advise.
thanks, mark
View 2 Replies
View Related
Aug 2, 2000
I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.
I need a query to do this.
Can anybody help.
-Rajesh
View 3 Replies
View Related
Sep 5, 2000
Hi,
I need to delete ONLY the duplicate rows from a table..Can anyone suggest me a way to do that thing..i used the query..
"Delete from <TableName> where SlNo IN ( Select SlNo from <TableName> group
by SlNO having Count(*) > 1)"
The resultant is all the original rows also getting deleted with the duplicates..anyone please help me out..
Thank you in advance
Rani
View 5 Replies
View Related
Jul 26, 2002
I have a customer database with the following structure:
Dept ID (int)
Section (varchar)
I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.
Example Data:
Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance
The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.
I am not in control of the data design and can't create a bitwise field to achieve this.
tia,
Steve
View 1 Replies
View Related
Aug 5, 2002
Hi,
I’m using SQL Server 2000. I have a table called Contacts and I would like to be able to have the UserID as an indexed column and to ignore duplicates. I set up the following properties within my SQL Server database table:
Table Name: Contacts
Selected Index: IX_UserID
Column Name: UserID
Order: Ascending
Create Unique
Index
Ignore Duplicate Key
Every time I try to enter duplicates for the UserID column; I get an error that says, “Cannot enter duplicate key row in object ‘Contacts’. Can anyone explain this? Is it possible to create an index column with duplicate data?
Thanks,
Denise
View 4 Replies
View Related
Sep 21, 2004
Hey all.
I have a table with 100,000 plus records in it, and some are duplicates. Is there any way to delete one of them and not the other. For instance, if I duplicate the table I could run this query.
<cfquery name="query1" datasource="datasource">
DELETE DISTINCT
FROM tablename
WHERE FirstName in ( SELECT FirstName from tablename1 where tablename1.FirstName = tablename.FIRST_NAME AND tablename1.LastName = tablename.LAST_NAME AND tablename1.State = tablename.STATE)
</cfquery>
However, it doesn't work. I know the distinct is not correct. But does anyone know how to achieve this, I have looked all over, and everything I try deletes both records. I was thinking of using some kindof count statement, but it still deletes both of them. Please help. Thanks
View 1 Replies
View Related
Jan 27, 2005
I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.
It doesnt matter which duplicate i remove as long as there are unique email addresses.
so here is the format of the table:
id
email
firstname
lastname
datebirth
if i do a:
SELECT COUNT(DISTINCT Email) AS Expr1
FROM Customer
it returns 21345
and
SELECT Count(Email)
FROM Customer
returns 28987
I can get the unique email addresses into another table by going:
SELECT DISTINCT emailaddress INTO DistinctCustomer
FROM Customer
but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?
Thanks in advance
Tom the SQL beginner
View 5 Replies
View Related
Jul 7, 2004
I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)
thank you
SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
FROM TERMINATION
GROUP BY [TM#]
HAVING Count([TM#])>1;
GO
View 14 Replies
View Related
Oct 22, 2004
Can some kind person out there please help me, I've been stuck on this for daaaa-y-s.
I have a database that allows users to search for pdf's of technical drawings.
Basically I have one huge table with multiple columns, which the user can only search on any combination of one of these two columns
"drawing_series" eg 0100, 0046, 1000
"drawing_number" eg 0076000, 0000123, 0000004
There is also a Revision column(which the user can't see) that goes up by 1 each time a drawing has been modified and resubmitted to the database.
"revision" eg 01, 02, 03, ....... 99
So a search on 0046 series might pull back drawings
0046-0010000-01
0046-0010000-02
0046-0010000-03
0046-0076000-01
0046-0076888-01
0046-0076888-02
The problem is that I only want drawings with the highest revisions returned eg
0046-0010000-03
0046-0076000-01
0046-0076888-02
The code below worked like a charm in the test stages pulling back a few hundred records but now that i've uploaded 10's of thousands of records to the DB the whole lot dies if the search result pulls back more than a few thousand records.
SELECT * FROM dbo.Drawing_Database
where dbo.Drawing_Database.revision=(select max(revision) from dbo.Drawing_Database self where self.drawing_series + self.drawing_number = dbo.Drawing_Database.drawing_series + dbo.Drawing_Database.drawing_number) Drawing_Series like '0046' order by Drawing_Series, Drawing_Number
There must be a simpler way of doing this as i can pull out duplicate series + numbers using " HAVING Count(*)>1" but dont know where to go from there.
Help!
TheMaster
View 2 Replies
View Related
