Query help

Hello Everyone,

Please can you help me with this?

I have an audit table, so there are many id's and modified dates... I am looking to get the last updated record for each ID. This is what I have so far... "which still gives duplicate ID's"

I have see many pages about distincta and max/min... I cannot make sense of it... HELP

1Select *2FROM TabelA3WhereExists (SELECT distinct max (id)as id ,max (ModifiedDate)as ModifiedDate4FROM TabelA)5Order by id

I tried to break it down, and still I get duplicate ID's

1Select *2FROM TabelA3Where idIN (SELECT distinct id4FROM TabelA)5Order by id

Try

SELECT *FROM TableAWHERECONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDEXISTS IN (selectCONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDas [key]from TableAgroup by ID)ORDER BY ID

|||

Thanks for your reply,

I get the following error

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'EXISTS'.

|||

SQL Server 2005:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate, row_number()OVER(partitionby idorderby ModifiedDateDESC)as RowNum

FROM TableA) t

WHERE t.RowNum=1

For SQL Server 2000, you can try:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate,(SELECTcount(*)FROM TABLEA aWHERE a.id=a1.idand a1.ModifiedDate<=a.ModifiedDate)as RowNum

FROM TableA a1) t

WHERE t.RowNum=1

|||

That's why your anAll-Star!....Shot for the help... it works very well.

Thanks B

sql

Query for "latest version" of a given row

I am maintaining historical versions of a row in a table. The idea is that a row is not really modified but a new row is created with a reference to the original row. (I guess a sort of generational view or row versioning...)

My problem is that I cannot come up with a query that will give me a list of the rows with only the latest version of the row included. My confusion is the self join -- the possibility (or lack of info before-hand) of how many parent/child rows there are from a given original row.)

Given the following table and data:

Code Snippet

CREATE TABLE Orders (
[OrderId] INT IDENTITY (1, 1),
[ClientName] VARCHAR(10),
[OriginalOrderId] INT
)
GO

INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Peter', NULL) -- should go in as 1
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Joseph', NULL) -- should go in as 2
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Mary', NULL) -- should go in as 3
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Jane', 2) -- should go in as 4 ("replaces" 2)
INSERT INTO Orders ([ClientName], [OriginalOrderId]) VALUES ('Paul', 4) -- should go in as 5 ("replaces" 4)
GO

SELECT * FROM Orders
-- need a query that will return me rows 1, 3, and 5
GO

DROP TABLE Orders
GO

The query should return me

Peter

Paul

Mary

I think another approach would be to replace the IDENTITY column with just an INT while adding a [Generation] or [Version] column. The join would be a simple self-join that figures out the maximum version value for a given Id. I will use this if I have to but I kinda like the idea of not having to manage the Id values myself.

What you have is fine. You just need to add an ORDER BY clause to the SELECT:

SELECT * FROM Orders
ORDER BY OrderID

or if you want the top 3:

SELECT TOP 3 * FROM Orders

ORDER BY OrderID

Adamus

|||

Ok revisiting this...you want to replace records?

This is done through UPDATE not INSERT.

The solution to your problem is adding a LastModified smalldatetime in conjuntion with the IDENTITY field.

Adamus

|||

Thank you for responding Adamus but you are not reading my post fully.

I am fully aware that UPDATE is for modifying existing records but that is not what I am trying to accomplish. My requirement is that instead of overwriting the existing row with the new columns, I need to insert a new row with the new values with that new row maintaining a "reference" to the original row. What I am looking for is a query that will return that "newest" from the set of chained rows. (It is clearly layed out if you look at the comments in the code snippet.)

|||

To do it with this design would likely require cursors or some other iterative approach. Your second idea is the right way to go as that would be a more set based approach like below:

Code Snippet

CREATE TABLE Orders (
[OrderId] INT IDENTITY (1, 1),
[ClientName] VARCHAR(10),
[ClientID] INT
)

INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Peter', 1) -- should go in as 1
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Joseph', 2) -- should go in as 2
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Mary', 3) -- should go in as 3
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Jane', 2) -- should go in as 4 ("replaces" 2)
INSERT INTO Orders ([ClientName], [ClientID]) VALUES ('Paul', 2) -- should go in as 5 ("replaces" 4)

SELECT ClientName
FROM
Orders
INNER JOIN
(
SELECT ClientID, MAX(OrderID) AS MaxorderID
FROM Orders
GROUP BY ClientID
) gr
ON Orders.ClientID = gr.ClientID
AND Orders.OrderID = gr.Maxorderid

go

|||

For clarity and organization, this should be accomplished with a dbo.History table.

You should not be trying to accomplish this in a single table. The documentation and query would be a maintenance nightmare.

You shouldn't be trying to re-invent the wheel on this. History tables are common practice.

Adamus

|||

Thanks Dave. I figured that was the way I would have to do it but I like to double check these things. In my case I need to maintain the history order (i.e. Joseph --> Jane --> Paul) so I will get rid of the IDENTITY column and all three of those rows will have the same OrderId value. I will also add a [Generation] column and the set them to 1, 2, 3 respectively (for J-->J-->P).

- Jason

|||

Adamus,

I greatly simplified the issue for purposes of posting. I cannot use a history file in this case because technically the "old" rows are still valid -- just happens that there is a "newer" version available. So in this case they all must be in the same table.

Just curious, how would your suggestion of a history table solve my query question? I would still be in the same boat trying to determine the newest version of a given set of rows. Like David mentioned, you would have to resort to cursors or recursive queries since maintaining the parent-child relationship does not automatically tell you how many levels you need to go down to find the last node.

|||

Jason Callas wrote:

Adamus,

I greatly simplified the issue for purposes of posting. I cannot use a history file in this case because technically the "old" rows are still valid -- just happens that there is a "newer" version available. So in this case they all must be in the same table.

Just curious, how would your suggestion of a history table solve my query question? I would still be in the same boat trying to determine the newest version of a given set of rows. Like David mentioned, you would have to resort to cursors or recursive queries since maintaining the parent-child relationship does not automatically tell you how many levels you need to go down to find the last node.

Jason,

As I have mentioned, it is very common that duplicate records are stored in a history table that houses the duplicate with a timestamp. An Instead Of INSERT trigger is used on the table to check for duplicates and insert duplicates into the history table.

A simply join to the history table will expose the chronology. No cursor is required.

Happy coding,

Adamus

|||

Jason Callas wrote:

Thanks Dave. I figured that was the way I would have to do it but I like to double check these things. In my case I need to maintain the history order (i.e. Joseph --> Jane --> Paul) so I will get rid of the IDENTITY column and all three of those rows will have the same OrderId value. I will also add a [Generation] column and the set them to 1, 2, 3 respectively (for J-->J-->P).

- Jason

(praying to God that I will never have to touch this code)

Adamus

Query assistance or advice

Hi,
I have a table which I copy nightly via DTS. I would like to copy only the
data which was changed instead based on the MODIFIED date. It will have to
insert any new rows created or update any row
which already exists. This is a sample
TABLE1
ID PRODID NAME QTY MODIFIED
1 123 TEST 1 2006-02-09
2 235 TEST2 2 2006-02-09
3 234 TEST3 5 2006-02-09
TABLE2 (MIRROR)
ID PRODID NAME QTY MODIFIED
1 123 TEST 5 2006-02-07
In this case when I run the query it will update TABLE2 by
updating the qty for id 1 to 1
insert id 2 and 3
Any ideas?
ThanksWhy don't you use triggers? There are several good examples in Books Online.
ML
http://milambda.blogspot.com/|||Doesn't DTS have tools for this? How are you using DTS? I think it has
tools to let you do this directly into table2, checking to see if it needs
to be done.
If you pumping the data right into a temporary table and then running a
query? If so then just write a query like :
insert into table2 (columnList)
select (columnList)
from table1changes as table1
where not exists (select 1
from table2
where table1.id = table2.id)
update table2
set table2.(each column) = table1.(each column)
from table2
join table1changes as table1
on table2.id = table1.id --this must be unique or you
will get (predictably) wierd results
and table1.modified <> table2.modified
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A8518925-0EB0-4D7D-8BD6-2C4FFBE686E4@.microsoft.com...
> Hi,
> I have a table which I copy nightly via DTS. I would like to copy only the
> data which was changed instead based on the MODIFIED date. It will have to
> insert any new rows created or update any row
> which already exists. This is a sample
> TABLE1
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 1 2006-02-09
> 2 235 TEST2 2 2006-02-09
> 3 234 TEST3 5 2006-02-09
> TABLE2 (MIRROR)
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 5 2006-02-07
>
> In this case when I run the query it will update TABLE2 by
> updating the qty for id 1 to 1
> insert id 2 and 3
> Any ideas?
> Thanks|||I am importing from a legacy database. I am currently transferring the entir
e
table nightly but it's hugh so we added a modifieddate column to the table s
o
now I want to check for any records added and updated for a specific date
then check my table in sql server, if records does not exists then insert
them if they do exists then update.
Thanks
"Chris" wrote:

> Hi,
> I have a table which I copy nightly via DTS. I would like to copy only the
> data which was changed instead based on the MODIFIED date. It will have to
> insert any new rows created or update any row
> which already exists. This is a sample
> TABLE1
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 1 2006-02-09
> 2 235 TEST2 2 2006-02-09
> 3 234 TEST3 5 2006-02-09
> TABLE2 (MIRROR)
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 5 2006-02-07
>
> In this case when I run the query it will update TABLE2 by
> updating the qty for id 1 to 1
> insert id 2 and 3
> Any ideas?
> Thanks|||For DTS I am using a transform data task form one data source to the other.
What tools are you talking about?
"Louis Davidson" wrote:

> Doesn't DTS have tools for this? How are you using DTS? I think it has
> tools to let you do this directly into table2, checking to see if it needs
> to be done.
> If you pumping the data right into a temporary table and then running a
> query? If so then just write a query like :
> insert into table2 (columnList)
> select (columnList)
> from table1changes as table1
> where not exists (select 1
> from table2
> where table1.id = table2.id)
> update table2
> set table2.(each column) = table1.(each column)
> from table2
> join table1changes as table1
> on table2.id = table1.id --this must be unique or you
> will get (predictably) wierd results
> and table1.modified <> table2.modified
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A8518925-0EB0-4D7D-8BD6-2C4FFBE686E4@.microsoft.com...
>
>|||I don't know. I am a query/design guy. DTS is a tool I have heard about
and read about but never put into practice. I would think that the
transform task might be able to do a query to check for row existance (maybe
someone else will know?)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F2580A5F-1EEB-4120-82C3-0CE0FF6FEA9C@.microsoft.com...
> For DTS I am using a transform data task form one data source to the
> other.
> What tools are you talking about?
> "Louis Davidson" wrote:
>