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...
>

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...
>

Find how an item ranks in a table?

Is it possible to find the position of an item without retrieving all the records in the table?

I have a products table, and want to find out how its sales are doing within a particular category.

My table consists of the following columns of interest:

ProductID, CategoryID, ItemsSold.

So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?

SELECT *FROM dbo.ProductsWHERE CategoryID = 10ORDER BY ItemsSold

Also, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?

Any help would be very much appreciated.

Thanks.

The only way I can see of doing this is by creating a store procedure that returns the required values. Note that the only part I am not sure about is if the insert will work with an ORDER BY.

EDIT : If you are using SQL Server 2005, then have a look into therow_number()function

e.g.

CREATE STOREDPROCEDURE getProductRank
(
@.categoryIDint,
@.productIDint
)AS
CREATE TABLE #t
(
IDint IDENTITY(1,1),
ProductIDint,
CategoryID,
ItemsSold
)
INSERT INTO #t
(SELECT *FROM dbo.Products
WHERE CatehoryID = @.categoryID
ORDER BY ItemsSold)
DECLARE @.rankint
SELECT @.rank = IDFROM #t
WHERE ProductID = @.productID
RETURN @.rank
GO

|||

Hi Jagdipa,

I am using SQL Express 2005 and your reference was very helpful, however it still didn't remove the problem that I didn't want to retrieve all the records in the Category, as I only require 1 of the Products from the table (apologies for being unclear on this in the first post, I think you noticed as you declared a parameter - ProductID = @.ProductID).

I would like to call an aggregate function, something similar to the SUM() function as I mentioned previously.

I'm a little new to SQL so only the basic statements, and don't even know if its possible to achieve what I'm looking for without creating a temporary view or table as you've done?

Thanks.

|||

For those looking for the answer to this question, I have modified this from a custom paging example (which there appears to be lots of the web) to find the rank of an item in a table.

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY ItemsSold DESC) AS ProductRankNo, * FROM Products WHERE CategoryID = 10) AS AllProducts

WHERE ProductID = 23

To find the TOP 10 TOPSELLERS, simply change the "WHERE ProductID = 23" to "WHERE ProductRankNo BETWEEN 1 AND 10".

Bear in mind this will only work on SQL Server 2005 databases.

Initially, I was worried that the inner SELECT call would retrieve ALL the records in the table, but this doesn't appear to be so.