Hi Guys,
I am using sql server 2000 and i want to find missing
number between 1 and 1000 in a table.
what is the query for that?
pls advice me.
RGDS
BijuAssuming you have another table called Numbers that contains all the
required numbers:
SELECT num
FROM Numbers
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE num = Numbers.num)
AND num BETWEEN 1 AND 1000
David Portas
SQL Server MVP
--|||If there's only one Num Missing, this will work...
Select Num - 1
From <TableName> T
Where Num Between 1 And 1001
And Not Exists
(Select * From <TableName>
Where Num = T.Num - 1)
If there's a possibility of multiple Sequential numbers missing,
asin
1
2
3
6
7
...
then Use David's solution
"bijupg" wrote:
> Hi Guys,
> I am using sql server 2000 and i want to find missing
> number between 1 and 1000 in a table.
> what is the query for that?
> pls advice me.
> RGDS
> Biju
>|||Or use SQL Server 2000's nice TABLE variable to create a control table:
-- Use the Edit menu's 'Replace Template Parameters...' command to replace
the your_table/your_field values
DECLARE @.control TABLE ( control_no INT PRIMARY KEY )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 1
-- Add control numbers to temp table
WHILE @.i Between 1 And 1000
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List missing values
SELECT t.*
FROM @.control t
LEFT JOIN <your_table, SYSNAME, > c ON t.control_no = c.<your_field,
SYSNAME, >
WHERE c.control_no Is Null|||If you want the starting of each gap, you can do:
SELECT nbr + 1
FROM tbl
WHERE NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.nbr = tbl.nbr + 1 )
AND nbr <= 1000 ;
If you want the start & end of each set of missing numbers:
SELECT t1.Nbr + 1 AS "start",
MIN( t2.Nbr ) - 1 AS "end"
FROM tbl t1
INNER JOIN tbl t2
ON t1.Nbr < t2.Nbr
GROUP BY t1.Nbr
HAVING MIN( t2.Nbr ) - t1.Nbr > 1;
If you want to list all the missing numbers, following the suggestions to
use a table of sequential numbers.
Anith
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
finding lower case data
I have a table with a VIN number column, I need to find all entries where th
e
VIN contains a lower case character, example '4d0131P2X4E146985'. This recor
d
should be included in the results because it contains 'd'.. What would be th
e
easiest way to write a query to return these, can I use regular expression i
n
T-SQL...?
Thanks,
DanAlien2_51,
Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column
in question. I guess it is using a case insensitive one. Use the COLLATE
clause to tell SQL Server which collation to use during the operation and us
e
a case sensitive one. You can also use function binary_checksum.
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where binary_checksum(c1) != binary_checksum(upper(c1))
AMB
"Alien2_51" wrote:
> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan|||You could do this:
select VIN
from yourTable
where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
-- or
select VIN
from yourTable
where VIN like '%[a-z]%'
collate Latin1_General_BIN
Note that it will not work to use the range [a-z] in the first
example, because in the CS_AS collation, [a-z] contains the
uppercase letters A through Y.
Steve Kass
Drew University
http://www.stevekass.com
Alien2_51 wrote:
>I have a table with a VIN number column, I need to find all entries where t
he
>VIN contains a lower case character, example '4d0131P2X4E146985'. This reco
rd
>should be included in the results because it contains 'd'.. What would be t
he
>easiest way to write a query to return these, can I use regular expression
in
>T-SQL...?
>Thanks,
>Dan
>|||Thanks Alejandro... Just what I needed, and so simple...
DB
"Alejandro Mesa" wrote:
> Alien2_51,
> Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the colu
mn
> in question. I guess it is using a case insensitive one. Use the COLLATE
> clause to tell SQL Server which collation to use during the operation and
use
> a case sensitive one. You can also use function binary_checksum.
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where binary_checksum(c1) != binary_checksum(upper(c1))
>
> AMB
>
> "Alien2_51" wrote:
>|||Alien, I guess you might want to check this out
http://vadivel.blogspot.com/2003/12...e-searches.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"Alien2_51" wrote:
> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan
e
VIN contains a lower case character, example '4d0131P2X4E146985'. This recor
d
should be included in the results because it contains 'd'.. What would be th
e
easiest way to write a query to return these, can I use regular expression i
n
T-SQL...?
Thanks,
DanAlien2_51,
Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column
in question. I guess it is using a case insensitive one. Use the COLLATE
clause to tell SQL Server which collation to use during the operation and us
e
a case sensitive one. You can also use function binary_checksum.
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where binary_checksum(c1) != binary_checksum(upper(c1))
AMB
"Alien2_51" wrote:
> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan|||You could do this:
select VIN
from yourTable
where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
-- or
select VIN
from yourTable
where VIN like '%[a-z]%'
collate Latin1_General_BIN
Note that it will not work to use the range [a-z] in the first
example, because in the CS_AS collation, [a-z] contains the
uppercase letters A through Y.
Steve Kass
Drew University
http://www.stevekass.com
Alien2_51 wrote:
>I have a table with a VIN number column, I need to find all entries where t
he
>VIN contains a lower case character, example '4d0131P2X4E146985'. This reco
rd
>should be included in the results because it contains 'd'.. What would be t
he
>easiest way to write a query to return these, can I use regular expression
in
>T-SQL...?
>Thanks,
>Dan
>|||Thanks Alejandro... Just what I needed, and so simple...
DB
"Alejandro Mesa" wrote:
> Alien2_51,
> Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the colu
mn
> in question. I guess it is using a case insensitive one. Use the COLLATE
> clause to tell SQL Server which collation to use during the operation and
use
> a case sensitive one. You can also use function binary_checksum.
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where binary_checksum(c1) != binary_checksum(upper(c1))
>
> AMB
>
> "Alien2_51" wrote:
>|||Alien, I guess you might want to check this out
http://vadivel.blogspot.com/2003/12...e-searches.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"Alien2_51" wrote:
> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan
Thursday, March 29, 2012
Finding last entries
Hi, I am searching for the most easy SQL solution:
Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
Thx for reply.
dajmHow's your resume' looking these days? Is it up to date?|||The last row updated? The last row inserted? Physical location might not really mean anything.
Are there any related tables that could help us out.
You might be screwed if you want to figure this out in your current setup.
Going forward you could use a trigger to populate a new table for this purpose.|||If you use TOP 1 you get a result that is as good as any.
Noone can say that it is false, because if they can, they must say why, and if they say why, then you have a rule you could use in your select statement.
That's what I should've done in your shoes, given that there was noone to ask that is ...
As Thrasymachus says, there is no way (with the given info) that the solution can be found, atleast not that I can see.|||O.K. Here's an example for everybody
Car-ID |sales price
1|5
3|3
2|6
1|7
3|4
As result I need to get
2|6
1|7
3|4
With Navision language C/Side it is very easy to catch the last entries of database by setting a filter on car-id, but how do I get this result by using SQL ?|||Example pasted from earlier post :
Car-ID |sales price
1|5
3|3
2|6
1|7
3|4
As result I need to get
2|6
1|7
3|4
Nothing tells you that these three last entries in your result is in fact the three last ones. All you know is that some means of presentation choose to show you those results in that order. Running the same question again or from another client might return other results. Therefore you still haven't presented any useful info to solve the problem, unless you wanna use my TOP solution earlier. In that case replace the TOP 1 with TOP 3 ...|||Data in a database is not guaranteed to be in any order unless specified in the table design. If this table has a clustered index (as it should), then the data is no longer in the order in which it was entered.
Period.
If you do not store the datetime the record was entered, you cannot expect to sort or filter on the datetime the record was entered.|||There exists no index, but the no. of cars is dynamic.
I cannot use a "select top n" statement as I never know "n".
Can I combine this however with a "select count (distinct car-id)" ?
dajm|||Sorry, all back. There is a clustered index on a integer field with incremental no., used as a uniqueidentifier.
Does this help somehow ?|||well yes. but you said there were no keys earlier no keys...
and then you said the keys were not dynamic
"Lets say i have 500000 rows of cars in one table with 30000 different car ID's"
just use the max() function on your newly discovered field.|||A max() on this field returns just the last row, but not every last row of my 30000 vehicles, means 30000 rows.|||See this thread for a solution to your problem:
http://www.dbforums.com/showthread.php?t=1094858&page=1&pp=15
Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
Thx for reply.
dajmHow's your resume' looking these days? Is it up to date?|||The last row updated? The last row inserted? Physical location might not really mean anything.
Are there any related tables that could help us out.
You might be screwed if you want to figure this out in your current setup.
Going forward you could use a trigger to populate a new table for this purpose.|||If you use TOP 1 you get a result that is as good as any.
Noone can say that it is false, because if they can, they must say why, and if they say why, then you have a rule you could use in your select statement.
That's what I should've done in your shoes, given that there was noone to ask that is ...
As Thrasymachus says, there is no way (with the given info) that the solution can be found, atleast not that I can see.|||O.K. Here's an example for everybody
Car-ID |sales price
1|5
3|3
2|6
1|7
3|4
As result I need to get
2|6
1|7
3|4
With Navision language C/Side it is very easy to catch the last entries of database by setting a filter on car-id, but how do I get this result by using SQL ?|||Example pasted from earlier post :
Car-ID |sales price
1|5
3|3
2|6
1|7
3|4
As result I need to get
2|6
1|7
3|4
Nothing tells you that these three last entries in your result is in fact the three last ones. All you know is that some means of presentation choose to show you those results in that order. Running the same question again or from another client might return other results. Therefore you still haven't presented any useful info to solve the problem, unless you wanna use my TOP solution earlier. In that case replace the TOP 1 with TOP 3 ...|||Data in a database is not guaranteed to be in any order unless specified in the table design. If this table has a clustered index (as it should), then the data is no longer in the order in which it was entered.
Period.
If you do not store the datetime the record was entered, you cannot expect to sort or filter on the datetime the record was entered.|||There exists no index, but the no. of cars is dynamic.
I cannot use a "select top n" statement as I never know "n".
Can I combine this however with a "select count (distinct car-id)" ?
dajm|||Sorry, all back. There is a clustered index on a integer field with incremental no., used as a uniqueidentifier.
Does this help somehow ?|||well yes. but you said there were no keys earlier no keys...
and then you said the keys were not dynamic
"Lets say i have 500000 rows of cars in one table with 30000 different car ID's"
just use the max() function on your newly discovered field.|||A max() on this field returns just the last row, but not every last row of my 30000 vehicles, means 30000 rows.|||See this thread for a solution to your problem:
http://www.dbforums.com/showthread.php?t=1094858&page=1&pp=15
Finding Indexes for a table
I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>
Finding Indexes for a table
I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>sql
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>sql
Finding Indexes for a table
I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>
finding foreign key info
Hi,
Is there a way to find out if a column on a table is a foreign key, what
table it references and what column on that table it is referencing. All of
my foreign keys are a single column, which should make things easier.
Any help would be greatly appreciated.
Regards,
NedYou can get this from a couple of INFORMATION_SCHEMA views, e.g.
select
pk_ccu.table_name as PK_Table,
pk_ccu.column_name as PK_Column,
fk_ccu.table_name as FK_Table,
fk_ccu.column_name as FK_Column
from
information_schema.constraint_column_usage pk_ccu
join information_schema.referential_constraints rc on
pk_ccu.constraint_name=rc.unique_constraint_name
join information_schema.constraint_column_usage fk_ccu on
rc.constraint_name=fk_ccu.constraint_name
where
fk_ccu.table_Name='table_in_question'
and fk_ccu.column_name='column_in_question'
Ned wrote:
> Hi,
> Is there a way to find out if a column on a table is a foreign key, what
> table it references and what column on that table it is referencing. All
of
> my foreign keys are a single column, which should make things easier.
> Any help would be greatly appreciated.
> Regards,
> Ned
>|||Thanks Trey,
Worked like a charm.
Regards,
Ned
"Trey Walpole" <treyNOpole@.comSPAMcast.net> wrote in message
news:exXjWn0rFHA.460@.TK2MSFTNGP15.phx.gbl...
> You can get this from a couple of INFORMATION_SCHEMA views, e.g.
> select
> pk_ccu.table_name as PK_Table,
> pk_ccu.column_name as PK_Column,
> fk_ccu.table_name as FK_Table,
> fk_ccu.column_name as FK_Column
> from
> information_schema.constraint_column_usage pk_ccu
> join information_schema.referential_constraints rc on
> pk_ccu.constraint_name=rc.unique_constraint_name
> join information_schema.constraint_column_usage fk_ccu on
> rc.constraint_name=fk_ccu.constraint_name
> where
> fk_ccu.table_Name='table_in_question'
> and fk_ccu.column_name='column_in_question'
>
> Ned wrote:
Is there a way to find out if a column on a table is a foreign key, what
table it references and what column on that table it is referencing. All of
my foreign keys are a single column, which should make things easier.
Any help would be greatly appreciated.
Regards,
NedYou can get this from a couple of INFORMATION_SCHEMA views, e.g.
select
pk_ccu.table_name as PK_Table,
pk_ccu.column_name as PK_Column,
fk_ccu.table_name as FK_Table,
fk_ccu.column_name as FK_Column
from
information_schema.constraint_column_usage pk_ccu
join information_schema.referential_constraints rc on
pk_ccu.constraint_name=rc.unique_constraint_name
join information_schema.constraint_column_usage fk_ccu on
rc.constraint_name=fk_ccu.constraint_name
where
fk_ccu.table_Name='table_in_question'
and fk_ccu.column_name='column_in_question'
Ned wrote:
> Hi,
> Is there a way to find out if a column on a table is a foreign key, what
> table it references and what column on that table it is referencing. All
of
> my foreign keys are a single column, which should make things easier.
> Any help would be greatly appreciated.
> Regards,
> Ned
>|||Thanks Trey,
Worked like a charm.
Regards,
Ned
"Trey Walpole" <treyNOpole@.comSPAMcast.net> wrote in message
news:exXjWn0rFHA.460@.TK2MSFTNGP15.phx.gbl...
> You can get this from a couple of INFORMATION_SCHEMA views, e.g.
> select
> pk_ccu.table_name as PK_Table,
> pk_ccu.column_name as PK_Column,
> fk_ccu.table_name as FK_Table,
> fk_ccu.column_name as FK_Column
> from
> information_schema.constraint_column_usage pk_ccu
> join information_schema.referential_constraints rc on
> pk_ccu.constraint_name=rc.unique_constraint_name
> join information_schema.constraint_column_usage fk_ccu on
> rc.constraint_name=fk_ccu.constraint_name
> where
> fk_ccu.table_Name='table_in_question'
> and fk_ccu.column_name='column_in_question'
>
> Ned wrote:
Finding first X records that when sumed up meet a criteria (Threshold)
Hi I'm tryin to do a tsql statement that returns the first X records that meet a certain criteria (Threshold):
Say the table has 2 columns: ID & Count
And 2 rows:
ID, Count
1, 10
2, 10
I'm looking to return rows and values until a threshold is reached, so
if threshold is 8, query should return:
ID 1 and Count 8
if theshold i s 15, query should return
ID 1, Count 10
ID 2, Count 5
Any help on this would be appreciated, Thanks!
Hey Zorca. First of all, I'll assume for now that you're running SQL 2000. If you're using SQL 2005, let me know and I'll give you a simpler solution using new SQL 2005 features.
To achieve this, you'd first need to write a query to get the running totals for your given table. You can do this as follows (note that I'm using the table name 'ztmp_table'):
create table dbo.ztmp_table (iid int, cnt int)
go
insert dbo.ztmp_table (iid, cnt) select 1,10
insert dbo.ztmp_table (iid, cnt) select 2,10
insert dbo.ztmp_table (iid, cnt) select 3,10
go
-- Get a running total of the cnt column for the table
select t1.iid, min(t1.cnt) as mycnt, sum(t2.cnt) as runcnt
from ztmp_table t1
join ztmp_table t2
on t2.iid <= t1.iid
group by t1.iid
order by t1.iid
go
Once you have that in place, you then use that query to fulfill the remainder of your mission. I'm going to simplify it by wrapping the above query in a view, removing the order by clause, as follows:
create view dbo.ztmp_runtotals as
-- Get a running total of the cnt column for the table
select t1.iid, min(t1.cnt) as mycnt, sum(t2.cnt) as runcnt
from ztmp_table t1
join ztmp_table t2
on t2.iid <= t1.iid
group by t1.iid
go
Now that I have that view to make simpler code, you can use the following script to see how you can achieve what you're looking for:
declare @.thresh int
set @.thresh = 15
select iid,
case
when runcnt > @.thresh then (@.thresh - (runcnt - mycnt))
when runcnt <= @.thresh then mycnt
end as cnt
from dbo.ztmp_runtotals as tmp1
where tmp1.iid <=
isnull((select min(iid) from dbo.ztmp_runtotals where runcnt >= @.thresh),iid)
order by tmp1.iid
I'm not going to spend a lot of time going into detail on how it works, I'll let you figure that part out, but feel free to repost any questions you may have. Play around with the @.thresh parameter a bit to see the different results you get.
HTH,
finding first record ... !?
I have a table as follows:
- user_id (key)
- user_email_address (text)
- user_request_date (text)
- user_sent_date (time/date)
An automated macro will run every 5 minutes and needs to run a SQL UPDATE
that:
- finds the FIRST (ONE ROW ONLY) (youngest) record where
- user_request_date is the most recent
- AND user_sent_date is NULL (i.e. blank)
- THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
current system date/time)
For example:
- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL
SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)
- update record 33 - with NOW() time and date
THEN record 34, then 35, then 36, then 37
ONE ROW at a time per each SQL UPDATE
Any ideas!?
Thanks!
Richard
richard @. rcbuchanan . comRichard,
Try something like
update Richard set
user_sent_date = getdate()
where user_id = (
select top 1 user_id from Richard R
where R.user_sent_date is null
order by user_request_date
)
Your narrative and your example don't agree on whether
you want the most recent (youngest), or least recent (oldest)
user_request_date, so you may need to add DESC to the
order by clause.
Steve Kass
Drew University
Richard C Buchanan wrote:
>I have a table as follows:
>- user_id (key)
>- user_email_address (text)
>- user_request_date (text)
>- user_sent_date (time/date)
>An automated macro will run every 5 minutes and needs to run a SQL UPDATE
>that:
>- finds the FIRST (ONE ROW ONLY) (youngest) record where
> - user_request_date is the most recent
> - AND user_sent_date is NULL (i.e. blank)
> - THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
>current system date/time)
>
>For example:
>- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
>- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
>- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
>- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
>- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL
>SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)
>- update record 33 - with NOW() time and date
>THEN record 34, then 35, then 36, then 37
>ONE ROW at a time per each SQL UPDATE
>
>Any ideas!?
>Thanks!
>Richard
>richard @. rcbuchanan . com
>
Finding duplicates in the table
source table AAA
-------
pvn_acct_id
acct_id
no primary key, there can be duplicates, for ex:
pvn_acct_id acct_id
======= =========
111 111
111 111
111 111
222 333
222 334
222 335
333 222
334 222
335 222
from first set, using the below query to extract data, it will result in 111,111, for 2nd set, 222, 335, for third set, 335, 222.
select distinct a.pvn_acct_id, a.acct_id
from owner.AAA where a.acct_id = (select max(b.acct_id) from tstg.t_acct_num_hist b
where a.pvn_acct_id = b.pvn_acct_id)
and a.pvn_acct_id = (select max(c.pvn_acct_id) from tstg.t_acct_num_hist c
where a.acct_id = c.acct_id)
Above query is directly populating the target table BBB
What I want to find out is a sample of data set where i can find different instances of acct_id where pvn_Acct_id is same, and vice versa.
For ex:
I want to know where pvn_acct_id is 222, acct_id is 333,334,335, not just acct_id=335.
pls help.Did you try this:
select distinct a.pvn_acct_id, a.acct_id
from owner.AAA a
;)|||select pvn_Acct_id from owner.AAA aaa where acct_id in (select acct_id from owner.BBB bbb
where aaa.pvn_acct_id != bbb.pvn_acct_id) order by pvn_Acct_id
select Acct_id from owner.AAA aaa where pvn_acct_id in (select pvn_acct_id from owner.BBB bbb
where aaa.acct_id != bbb.acct_id) order by Acct_id
now this query works. got it with trial and error. have fun testing.
-------
pvn_acct_id
acct_id
no primary key, there can be duplicates, for ex:
pvn_acct_id acct_id
======= =========
111 111
111 111
111 111
222 333
222 334
222 335
333 222
334 222
335 222
from first set, using the below query to extract data, it will result in 111,111, for 2nd set, 222, 335, for third set, 335, 222.
select distinct a.pvn_acct_id, a.acct_id
from owner.AAA where a.acct_id = (select max(b.acct_id) from tstg.t_acct_num_hist b
where a.pvn_acct_id = b.pvn_acct_id)
and a.pvn_acct_id = (select max(c.pvn_acct_id) from tstg.t_acct_num_hist c
where a.acct_id = c.acct_id)
Above query is directly populating the target table BBB
What I want to find out is a sample of data set where i can find different instances of acct_id where pvn_Acct_id is same, and vice versa.
For ex:
I want to know where pvn_acct_id is 222, acct_id is 333,334,335, not just acct_id=335.
pls help.Did you try this:
select distinct a.pvn_acct_id, a.acct_id
from owner.AAA a
;)|||select pvn_Acct_id from owner.AAA aaa where acct_id in (select acct_id from owner.BBB bbb
where aaa.pvn_acct_id != bbb.pvn_acct_id) order by pvn_Acct_id
select Acct_id from owner.AAA aaa where pvn_acct_id in (select pvn_acct_id from owner.BBB bbb
where aaa.acct_id != bbb.acct_id) order by Acct_id
now this query works. got it with trial and error. have fun testing.
Wednesday, March 28, 2012
Finding Duplicates
I have a company table and I would like to write a query that will return to
me any duplicate companies. However, it is a little more complicated then
just matching on exact company names. I would like it to give me duplicates
where x number of letters at the beginning of the company name match AND x
number of letters of the address match AND x number of letters of the city
match. I will be doing this in batches based on the first letter of the
company name. So for example I will first process all companies that start
with the letter "A".
me any duplicate companies. However, it is a little more complicated then
just matching on exact company names. I would like it to give me duplicates
where x number of letters at the beginning of the company name match AND x
number of letters of the address match AND x number of letters of the city
match. I will be doing this in batches based on the first letter of the
company name. So for example I will first process all companies that start
with the letter "A".
So for all "A" companies I want to find companies where the first 5 letters
in the company name match and the first 5 characters of the address field
match and the first 5 characters of the city match. THANKS!!!Can you post simplified DDLs, some sample data & expected results? For
detail refer to : www.aspfaq.com/5006
--
- Anith
( Please reply to newsgroups only )|||"Erich" <erich93063@.hotmail.com> wrote in message news:<102gbomj7gc84f7@.corp.supernews.com>...
> I have a company table and I would like to write a query that will return to
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me duplicates
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
> So for all "A" companies I want to find companies where the first 5 letters
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!!
Something like this may work:
select t.*
from dbo.MyTable t
join
(
select
left(CompanyName, 5) as 'CompName',
left(Address, 5) as 'Addr',
left(City, 5) as 'City',
count(*) as 'Dupes'
from
dbo.MyTable
where
left(CompanyName, 1) = 'A'
group by
left(CompanyName, 5),
left(Address, 5),
left(City, 5)
having
count(*) > 1
) dt
on dt.CompName = left(t.CompanyName, 5)
and dt.Addr = left(t.Address, 5)
and dt.City = left(t.City, 5)
If this doesn't work as you expect, then please consider posting your
table DDL, as well as some sample data.
Simon|||This may work as well, assuming a parameter is passed into the stored proc
or function for the first letter:
SELECT C1.CompanyID, C2.CompanyID,
C1.CompanyName, C2.CompanyName,
C1.Address, C2.Address, C1.City, C2.City
FROM Company C1 JOIN Company C2 ON
LEFT(C1.CompanyName, 5) = LEFT(C2.CompanyName, 5) AND
LEFT(C1.Address, 5) = LEFT(C2.Address, 5) AND
LEFT(C1.City, 5) = LEFT(C2.City, 5) AND
C1.CompanyID != C2.CompanyID
WHERE LEFT(C1.CompanyName, 1) = @.FirstLetter
You could also use a variable parameter instead of hard-coding "5" to allow
for more specific or more general matches.
ie, ... LEFT(C1.Company, @.MatchLength) = LEFT(C2.CompanyName, @.MatchLength)
"Erich" <erich93063@.hotmail.com> wrote in message
news:102gbomj7gc84f7@.corp.supernews.com...
> I have a company table and I would like to write a query that will return
to
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me
duplicates
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
> So for all "A" companies I want to find companies where the first 5
letters
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!!
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
> > >
> > >
> > >.
> > >
>
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
> > >
> > >
> > >.
> > >
>
Finding duplicate values in fields
Hi everyone..
I have a table with about 40,000 records in it.
I want to find records where all of the values in all of the fields, except for the unique field are equal.
The records in this table are answers to a online survey.
So, I want to find all of the records where people answered the questions exactly the same as someone else.
Any help would be appreciated.
NickieWhat about this:
select list of fields
from yourtable
group by list of fields having count(*)>1
I have a table with about 40,000 records in it.
I want to find records where all of the values in all of the fields, except for the unique field are equal.
The records in this table are answers to a online survey.
So, I want to find all of the records where people answered the questions exactly the same as someone else.
Any help would be appreciated.
NickieWhat about this:
select list of fields
from yourtable
group by list of fields having count(*)>1
finding duplicate values
Here's my situation: I work at a healthcare testing labratory, I have a table with all our tests, Dim_Test. The two columns i want to look at are: [Mnemonic] and [Test Name]. Mnemonic is the short test name and Test Name is a long name. The condition of the data is that there are a few duplicate mnemonics, but the the duplicates have different long test names. I know this is really messed up but thats our lab system for you, i have no control over that. I want to make a table that has all mnemonics that are duplicated, and the Long test name for each of the duplications. I have come up with a couple possible solutions but i dont know how to implement them or which would be the best course of action.
The First: Sort on the Mnemonic and delete duplicates, store that to a temp table. Then somehow compare Dim_test and the temp table to find the rows missing from the temp table and then store that to, say, temp2. So temp2 would contain all the data that was deleted during the deletion of duplicates on Dim_test. Then i could use the input Dim_test and do a lookup on table temp2 which would give me all mnemonics and test names with duplicate mnemonics.
The Second and third: Somehow get a dataset that has the distinct mnemonic and the count of each distinct mnemonic. Then i could use a conditional split to find out which mnemonics have a count of greater than one. then i can store that to a temp table and lookup on that. That temp table would be the same as temp2 in the first solution. The difference between the second and third solutions is that one uses an aggregate transformation and the other uses a pivot transformation.
I think the pivot transformation would work best if i pivoted on mnemonic but i dont know how to set it up. Please help. Thank you.
You don't need SSIS for this. This T-SQL will do the trick:
Code Snippet
use tempdb
go
CREATE Table [Dim_Test]
(
[Mnemonic] NVARCHAR (20),
[Test Name] NVARCHAR (200)
)
INSERT INTO [Dim_Test] VALUES ('AAA', 'This is a long test name 1')
INSERT INTO [Dim_Test] VALUES ('BBB', 'This is a long test name 2')
INSERT INTO [Dim_Test] VALUES ('CCC', 'This is a long test name 3')
INSERT INTO [Dim_Test] VALUES ('DDD', 'This is a long test name 4')
INSERT INTO [Dim_Test] VALUES ('EEE', 'This is a long test name 5')
INSERT INTO [Dim_Test] VALUES ('AAA', 'This is a long test name 6')
INSERT INTO [Dim_Test] VALUES ('FFF', 'This is a long test name 7')
INSERT INTO [Dim_Test] VALUES ('BBB', 'This is a long test name 8')
INSERT INTO [Dim_Test] VALUES ('AAA', 'This is a long test name 9')
GO
WITH DuplicatesCTE
AS
(
SELECT [Mnemonic]
FROM [Dim_Test]
GROUP BY [Mnemonic]
HAVING COUNT (*) > 1
)
SELECT [Dim_Test].*
FROM [Dim_Test]
INNER JOIN [DuplicatesCTE]
ON [Dim_Test].[Mnemonic] = [DuplicatesCTE].[Mnemonic]
If your source system is using SQL 2000 you will have to replace the CTE with a derived table, but the technique is the same.
Does this give you what you need?
|||select a.mnemonic, a.[test name] from dim_test a inner join(select mnemonic, count(*) from dim_test group by mnemonic having count(*) > 1) b
on a.mnemonic = b.mnemonic
This should give you a list of all of the duplicate mnemonics with their [test name]s.
If you want to create a NEW table with this information add an "into tablename" clause like so:
select a.mnemonic, a.[test name] into tablename
from dim_test a inner join
(select mnemonic, count(*) from dim_test group by mnemonic having count(*) > 1) b
on a.mnemonic = b.mnemonic|||
Both of these seemed to work, i ended up using Matthew's solution. I should really learn more about the SQL language, i pretty much do everything in SSIS. Thank you both very much.
Matthew could you please help me compact this. Please excuse my ignorance of the basic SQL language. So all the data is in Dim_test and the part i left out before was that there is also data with duplicate Mnemonics and Test Names example:
VALUES ('AAA', 'This is a long test name 1')
VALUES ('AAA', 'This is a long test name 1')
So this is what i want to do in a Stored procedure, and I need to do it without changing any data in Dim_test:
1. Disregard all duplicates of Mnemonic AND [Test Name] in Dim_test
2. DELETE FROM DupMnemonics
3. Store the results of your query in DupMnemonics
Thank you so much.
This is the query i ended up using
WITH DuplicatesCTE AS (SELECT Mnemonic
FROM DupMnemonics
GROUP BY Mnemonic
HAVING (COUNT(*) > 1))
SELECT DupMnemonics_1.Mnemonic, DupMnemonics_1.[Test Name]
FROM DupMnemonics AS DupMnemonics_1 INNER JOIN
DuplicatesCTE AS DuplicatesCTE_1 ON DupMnemonics_1.Mnemonic = DuplicatesCTE_1.Mnemonic
You can use a CTE to do this as well, when used in combination with the ROW_NUMBER ranking funcctiokn. Here's an example:
http://www.eggheadcafe.com/software/aspnet/29974931/removing-duplicate-rows-.aspx
|||
MatthewRoche wrote:
You can use a CTE to do this as well, when used in combination with the ROW_NUMBER ranking funcctiokn. Here's an example:
http://www.eggheadcafe.com/software/aspnet/29974931/removing-duplicate-rows-.aspx
Yeah, to the OP, there are so many ways to achieve this, you may want to move your discussions to the Transact-SQL forum. This is, after all, off topic in the SSIS forum... 
Is there a way to just move this thread over to T-SQL
Thank you all.
|||
Is there a way to just move this thread over to T-SQL
Thank you all.
There is. I'll do that now for you.
Finding duplicate records using two columns
I'm trying to find duplicate records in a table. I know how to do that using
a single column value, but I need to do it using the combined values of two
columns, company_id and client_id. There multiple are records with the same
company_id value (e.g., 123) and multiple records with the same department_id
value (e.g., 456). But there should be only one record with a company_id
value of 123 and a department_id value of 456.
I've searched for data in one table that is not in another table on these
two columns using CAST to concatenate the values:
select * from company c
where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
not in
(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
from client r)
But I can't seem to figure out how to use CAST to find duplicate records in
a single table. Should I be doing this a different way?
Any and all help would be appreciated.
JohnSELECT company_id, client_id, count(*) as Duplicates
FROM Company
GROUP BY company_id, client_id
HAVING COUNT(*) > 1
This works great with one column or ten columns.
Roy Harvey
Beacon Falls, CT
On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:
>I'm trying to find duplicate records in a table. I know how to do that using
>a single column value, but I need to do it using the combined values of two
>columns, company_id and client_id. There multiple are records with the same
>company_id value (e.g., 123) and multiple records with the same department_id
>value (e.g., 456). But there should be only one record with a company_id
>value of 123 and a department_id value of 456.
>I've searched for data in one table that is not in another table on these
>two columns using CAST to concatenate the values:
>select * from company c
>where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
>not in
>(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
>from client r)
>But I can't seem to figure out how to use CAST to find duplicate records in
>a single table. Should I be doing this a different way?
>Any and all help would be appreciated.
>John|||Thank you, Roy! It worked perfectly.
John
"Roy Harvey (SQL Server MVP)" wrote:
> SELECT company_id, client_id, count(*) as Duplicates
> FROM Company
> GROUP BY company_id, client_id
> HAVING COUNT(*) > 1
> This works great with one column or ten columns.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
> <moderndads(nospam)@.hotmail.com> wrote:
> >I'm trying to find duplicate records in a table. I know how to do that using
> >a single column value, but I need to do it using the combined values of two
> >columns, company_id and client_id. There multiple are records with the same
> >company_id value (e.g., 123) and multiple records with the same department_id
> >value (e.g., 456). But there should be only one record with a company_id
> >value of 123 and a department_id value of 456.
> >
> >I've searched for data in one table that is not in another table on these
> >two columns using CAST to concatenate the values:
> >
> >select * from company c
> >where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
> >not in
> >(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
> >from client r)
> >
> >But I can't seem to figure out how to use CAST to find duplicate records in
> >a single table. Should I be doing this a different way?
> >
> >Any and all help would be appreciated.
> >
> >John
>
a single column value, but I need to do it using the combined values of two
columns, company_id and client_id. There multiple are records with the same
company_id value (e.g., 123) and multiple records with the same department_id
value (e.g., 456). But there should be only one record with a company_id
value of 123 and a department_id value of 456.
I've searched for data in one table that is not in another table on these
two columns using CAST to concatenate the values:
select * from company c
where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
not in
(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
from client r)
But I can't seem to figure out how to use CAST to find duplicate records in
a single table. Should I be doing this a different way?
Any and all help would be appreciated.
JohnSELECT company_id, client_id, count(*) as Duplicates
FROM Company
GROUP BY company_id, client_id
HAVING COUNT(*) > 1
This works great with one column or ten columns.
Roy Harvey
Beacon Falls, CT
On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:
>I'm trying to find duplicate records in a table. I know how to do that using
>a single column value, but I need to do it using the combined values of two
>columns, company_id and client_id. There multiple are records with the same
>company_id value (e.g., 123) and multiple records with the same department_id
>value (e.g., 456). But there should be only one record with a company_id
>value of 123 and a department_id value of 456.
>I've searched for data in one table that is not in another table on these
>two columns using CAST to concatenate the values:
>select * from company c
>where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
>not in
>(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
>from client r)
>But I can't seem to figure out how to use CAST to find duplicate records in
>a single table. Should I be doing this a different way?
>Any and all help would be appreciated.
>John|||Thank you, Roy! It worked perfectly.
John
"Roy Harvey (SQL Server MVP)" wrote:
> SELECT company_id, client_id, count(*) as Duplicates
> FROM Company
> GROUP BY company_id, client_id
> HAVING COUNT(*) > 1
> This works great with one column or ten columns.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
> <moderndads(nospam)@.hotmail.com> wrote:
> >I'm trying to find duplicate records in a table. I know how to do that using
> >a single column value, but I need to do it using the combined values of two
> >columns, company_id and client_id. There multiple are records with the same
> >company_id value (e.g., 123) and multiple records with the same department_id
> >value (e.g., 456). But there should be only one record with a company_id
> >value of 123 and a department_id value of 456.
> >
> >I've searched for data in one table that is not in another table on these
> >two columns using CAST to concatenate the values:
> >
> >select * from company c
> >where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
> >not in
> >(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
> >from client r)
> >
> >But I can't seem to figure out how to use CAST to find duplicate records in
> >a single table. Should I be doing this a different way?
> >
> >Any and all help would be appreciated.
> >
> >John
>
Finding Duplicate Records
CREATE TABLE KeyLetter(
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>
Finding Duplicate Names in a Simple Table
This is probably so easy it will be stupid but I cannot get anything to
work.
I need to find, from one table, all duplicate names. I pull the entire list
like this:
SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
FROM Attendants
What I need from this is a list where the names of the clients appear more
than once. I know there has to be an easy way to do this. Any quick help'SELECT First_Name,last_Name, count(*)
FROM Attendants
group by first_name, last_name
having count(*) > 1
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||SELECT ID, RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name) , Date_of_Birth
FROM Attendants AS a
WHERE EXISTS (SELECT RTRIM(b.First_Name) + ' ' + RTRIM(b.Last_Name)
FROM Attendants AS b
WHERE a.First_Name = b.First_Name and
a.Last_Name = b.Last_Name
GROUP BY RTRIM(b.First_Name) + ' ' +
RTRIM(b.Last_Name)
HAVING COUNT(*) > 1 )
ORDER BY RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name)
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||Thanks Geoff
Works GREAT!
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:u0lwu%237%23HHA.1164@.TK2MSFTNGP02.phx.gbl...
> SELECT First_Name,last_Name, count(*)
> FROM Attendants
> group by first_name, last_name
> having count(*) > 1
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
>> This is probably so easy it will be stupid but I cannot get anything to
>> work.
>> I need to find, from one table, all duplicate names. I pull the entire
>> list like this:
>> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
>> FROM Attendants
>> What I need from this is a list where the names of the clients appear
>> more than once. I know there has to be an easy way to do this. Any quick
>> help'
>
work.
I need to find, from one table, all duplicate names. I pull the entire list
like this:
SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
FROM Attendants
What I need from this is a list where the names of the clients appear more
than once. I know there has to be an easy way to do this. Any quick help'SELECT First_Name,last_Name, count(*)
FROM Attendants
group by first_name, last_name
having count(*) > 1
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||SELECT ID, RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name) , Date_of_Birth
FROM Attendants AS a
WHERE EXISTS (SELECT RTRIM(b.First_Name) + ' ' + RTRIM(b.Last_Name)
FROM Attendants AS b
WHERE a.First_Name = b.First_Name and
a.Last_Name = b.Last_Name
GROUP BY RTRIM(b.First_Name) + ' ' +
RTRIM(b.Last_Name)
HAVING COUNT(*) > 1 )
ORDER BY RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name)
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||Thanks Geoff
Works GREAT!
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:u0lwu%237%23HHA.1164@.TK2MSFTNGP02.phx.gbl...
> SELECT First_Name,last_Name, count(*)
> FROM Attendants
> group by first_name, last_name
> having count(*) > 1
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
>> This is probably so easy it will be stupid but I cannot get anything to
>> work.
>> I need to find, from one table, all duplicate names. I pull the entire
>> list like this:
>> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
>> FROM Attendants
>> What I need from this is a list where the names of the clients appear
>> more than once. I know there has to be an easy way to do this. Any quick
>> help'
>
Finding duplicate entries in a "smart" way - by comparing first two words
What is the best way to compare two entries in a single table where
the two fields are "almost" the same?
the two fields are "almost" the same?
For example, I would like to write a query that would compare the
first two words in a "company" field. If they are the same, I would
like to output them.
For example, "20th Century" and "20th Century Fox" in the company
field would be the same.
How do I do this? Do I need to use a cursor? Is it as simple as using
"Like?"Here's an example:
CREATE TABLE Sometable (company VARCHAR(20) PRIMARY KEY)
INSERT INTO Sometable VALUES ('20th Century')
INSERT INTO Sometable VALUES ('20th Century Fox')
INSERT INTO Sometable VALUES ('Something Else')
SELECT company
FROM Sometable AS S
JOIN
(SELECT first_two_words
FROM
(SELECT company,
LEFT(company,
CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
AS first_two_words
FROM Sometable) AS X
GROUP BY first_two_words
HAVING COUNT(*)>1) AS T
ON T.first_two_words =
LEFT(company,CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
--
David Portas
----
Please reply only to the newsgroup
--|||>> a query that would compare the first two words in a "company"
field [sic]. If they are the same, I would like to output them. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY,
company CHAR(35) NOT NULL,
...);
SELECT F1.foo_key, F2.foo_key
FROM Foobar AS F1, Foobar AS F2
WHERE CHARINDEX(F1.company, F2.company) = 1
AND F1.foo_key < F2.foo_key;
Finding duplicate based on records in same table
I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...
paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join (select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...
|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...
>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegro ups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...
paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join (select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...
|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...
>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegro ups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
Finding duplicate based on records in same table
I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected resul
t?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...
>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
[url]http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html[/
url]
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected resul
t?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...
>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
[url]http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html[/
url]
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
Subscribe to:
Posts (Atom)