finding duplicates

quick easy querstion:
I have an inventory table in a datbase and each record has
a serial number. what is the sql code to find duplicates.
tiaSee following example:
create table #cartype(manufacturer varchar(500), score int)
insert into #cartype values('Toyota', 1 )
insert into #cartype values('Toyota', 2)
insert into #cartype values('Toyota', 3)
insert into #cartype values('Honda', 2)
insert into #cartype values('Honda', 5)
insert into #cartype values('Honda', 8)
insert into #cartype values('GE', 1)
insert into #cartype values('GE', 3)
insert into #cartype values('GE', 9)
insert into #cartype values('GE1', 6)
Following query will give you manufacturers that are appearing more than
once.
select manufacturer from #cartype
group by manufacturer
having count(*) > 1
--Following is the query to get the complete row details
select a.* from #cartype a inner join
(select manufacturer from #cartype
group by manufacturer
having count(*) > 1) b
on a.manufacturer = b.manufacturer
--
-Vishal
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||Katie,
SELECT serial_number, COUNT(serial_number) [Count]
FROM dbo.inventory
GROUP BY serial_number
HAVING COUNT(serial_number) > 1
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||SELECT serial_number FROM inventory
GROUP BY serial_number
HAVING COUNT(*) > 1
And if you want the actual rows:
SELECT * FROM inventory i
INNER JOIN (
SELECT serial_number FROM inventory
GROUP BY serial_number
HAVING COUNT(*) > 1) AS j
ON i.serial_number = j.serial_number
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||not very sure but,
I think you are using wrong column name, make sure all the column names that
you use in the query exists in the table.(may be some spelling mistake)
--
-Vishal
"katie" <kadf@.hsd.com> wrote in message
news:00c801c35062$c8f3e4f0$a101280a@.phx.gbl...
> i am doing this in access and it asks me to put a
> parameter value in. what does this mean
> >--Original Message--
> >Katie,
> >
> >SELECT serial_number, COUNT(serial_number) [Count]
> >FROM dbo.inventory
> >GROUP BY serial_number
> >HAVING COUNT(serial_number) > 1
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"katie" <kari@.wom3c.com> wrote in message
> >news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> >> quick easy querstion:
> >> I have an inventory table in a datbase and each record
> has
> >> a serial number. what is the sql code to find
> duplicates.
> >>
> >> tia
> >
> >
> >.
> >|||I think square brackets are used in Access to indicate a parameter, so if
you leave them of you should be ok. Note that I last used Access quite a few
years ago, so don't bank on it :-)
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:u7VqUTGUDHA.1912@.tk2msftngp13.phx.gbl...
> not very sure but,
> I think you are using wrong column name, make sure all the column names
that
> you use in the query exists in the table.(may be some spelling mistake)
> --
> -Vishal
> "katie" <kadf@.hsd.com> wrote in message
> news:00c801c35062$c8f3e4f0$a101280a@.phx.gbl...
> > i am doing this in access and it asks me to put a
> > parameter value in. what does this mean
> > >--Original Message--
> > >Katie,
> > >
> > >SELECT serial_number, COUNT(serial_number) [Count]
> > >FROM dbo.inventory
> > >GROUP BY serial_number
> > >HAVING COUNT(serial_number) > 1
> > >
> > >--
> > >Dinesh.
> > >SQL Server FAQ at
> > >http://www.tkdinesh.com
> > >
> > >"katie" <kari@.wom3c.com> wrote in message
> > >news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> > >> quick easy querstion:
> > >> I have an inventory table in a datbase and each record
> > has
> > >> a serial number. what is the sql code to find
> > duplicates.
> > >>
> > >> tia
> > >
> > >
> > >.
> > >
>