Simple/General SQL Question

[Justin]

I have a table that we use for audit trail purposes and I need to
evaluate the last row that was enetered for a particular case to see
if it meets certain criteria and if it does I need to return all the
rows that pertain to that case. Thanks in advance for the help.

[Erland Sommarskog]

Justin (ju**************@noridian.com) writes:

I have a table that we use for audit trail purposes and I need to
evaluate the last row that was enetered for a particular case to see
if it meets certain criteria and if it does I need to return all the
rows that pertain to that case. Thanks in advance for the help.

Well, that depends on whether there is something in the data in that
row that makes it possible to identify it as the "last row".

Assuming that the primary key is (caseno, rowno) and the rowno reflects
the entered order, this could do:

IF EXISTS (SELECT *
FROM cases c
JOIN (SELECT rowno = MAX(rowno), caseno
FROM cases
GROUP BY caseno) AS M ON c.caseno = m.caseno
WHERE c.caseno = @caseno
AND <your criteria goes here>)
SELECT * FROM cases WHERE caseno = @caseno

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Muzzy]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Justin (ju**************@noridian.com) writes:

I have a table that we use for audit trail purposes and I need to
evaluate the last row that was enetered for a particular case to see
if it meets certain criteria and if it does I need to return all the
rows that pertain to that case. Thanks in advance for the help.

Well, that depends on whether there is something in the data in that
row that makes it possible to identify it as the "last row".

Assuming that the primary key is (caseno, rowno) and the rowno reflects
the entered order, this could do:

IF EXISTS (SELECT *
FROM cases c
JOIN (SELECT rowno = MAX(rowno), caseno
FROM cases
GROUP BY caseno) AS M ON c.caseno = m.caseno
WHERE c.caseno = @caseno
AND <your criteria goes here>)
SELECT * FROM cases WHERE caseno = @caseno

You could also add an identity field, so every time you know which record is
the last by querying

SELECT * from <table> where <identity_field> = max(<identity_field>)

[Erland Sommarskog]

Muzzy (le*******@yahoo.com) writes:

You could also add an identity field, so every time you know which
record is the last by querying

SELECT * from <table> where <identity_field> = max(<identity_field>)

The only difference between an IDENTITY column and the rowno I
suggested, is that the IDENTITY column is a row number on table-wide
basis (and the fact that it has the IDENTITY property is irrelevant),
while my row number is one by case basis. Since the table appears to hold
entries about cases, it appears logical to have the case number as the first
column in the primary key. Of course, Justin's actual table may look com-
pletely different, but since he did not supply information, I worked from
the most reasonable design. If the table does not have a row number, but
a global id, the query would be the as the one I posted.

The one you suggest does not work, and would give a syntax error. Even
if you replace max(<identity_field>) with a subquery to fix the syntax,
it would give you the most recently entered row for any case, and not a
particular case, which was what Justin asked for.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Justin Torkelson]

Thanks for all the help. You're showing paths that have not yet taken.
Sorry to provide you with so little info about my table. I haven't had
good luck with postings so I have a tough time putting the time and
effort into them. Now, on to my problem: This table has several
different columns that I need to return but only need three to evaluate
if I should return it or not. Like I said this is an audit trail table
that tells a story of what happened to a particular case. Column one is
the case number, column two is a sequence number (these two make up the
PK) and column three is a process status. I need to look at the last
entry for a case number (highest sequence number) evaluate the Process
status to see if the case is closed and if it is I need to return the
"story" of the case (all the sequence numbers for that case number.
Thanks again for all the help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Justin]

Thanks for all the help. You're showing paths that have not yet taken.
Sorry to provide you with so little info about my table. I haven't had
good luck with postings so I have a tough time putting the time and
effort into them. Now, on to my problem: This table has several
different columns that I need to return but only need three to
evaluate
if I should return it or not. Like I said this is an audit trail table
that tells a story of what happened to a particular case. Column one
is
the case number, column two is a sequence number (these two make up
the
PK) and column three is a process status. I need to look at the last
entry for a case number (highest sequence number) evaluate the Process
status to see if the case is closed and if it is I need to return the
"story" of the case (all the sequence numbers for that case number.
Thanks again for all the help.

Again, forgive me for my lack of knowledge, but I have never used
variables in SQL before. Why are they used in the above example?
Thanks

[Justin]

PROBLEM SOLVED

SELECT *
FROM cases a
WHERE EXISTS
(SELECT *
FROM cases b
WHERE a.case_num = b.case_num AND
(SELECT max(seq_num) most
From cases c
WHERE c.Case_num = b.case_num) = b.seq_num AND
b.Process_Status not in (70, 90, 91, 92))

If there is anymore input or other thoughts please let me know. I've
done some initial testing and it seems to work but I still have more
testing to do.

[Erland Sommarskog]

Justin (ju**************@noridian.com) writes:

Again, forgive me for my lack of knowledge, but I have never used
variables in SQL before. Why are they used in the above example?

Because you said that you were looking for a specific case, I had
to throw that in somewhere. I was assuming that were using a stored
procedure, in which case the case number was a parameter.

But you can see it just as a place holder.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp