SEO

Here's what I'm trying to do:

I have a table, which I don't have administration over, with 4 interestingfields:
RecordCount - an auto incrementing primary key
CardNumber - integer
CompanyID - integer
AccessPriv - an string that varies constantly

There could be identical CardNumber's, but they must have differentCompanyId's. A data set might look like this:

RecordCount | CardNumber | CompanyID | AccessPriv
1 | 1 | 82 | all
2 | 2 | 82 | level 1
3 | 2 | 84 | all
4 | 1 | 82 | none

The table is transactional, so old records will not be flushed even thoughnew records contain the most current data (records 1 and 4 in this case). I'm wondering if there's a way to do a SQL SELECT query that, as it goesfrom the beginning to the end of the table, overwrites previous recordswhen later CardNumber's and CompanyID's match the previous records. So inthis case, the query would only return rows 2-4 because record 4'sCardNumber and CompanyID match record 1's.

I know this is possible with application logic, but is it possible with anSQL query?

Thanks.

SELECT S1.*
FROM Sometable AS S1
JOIN
(SELECT MAX(recordcount)
FROM Sometable
GROUP BY cardnumber, companyid)
AS S2(recordcount)
ON S1.recordcount = S2.recordcount

(untested)

--
David Portas
------------
Please reply only to the newsgroup
--

You could try this:

SELECT *
FROM _table_ t1
INNER JOIN (
SELECT MAX(RecordCount) as LastRecordCount FROM _table_
GROUP BY CardNumber, CompanyID ) as t2 ON t1.RecordCount =
t2.LastRecordCount


Hope this helps.
Igor Raytsin


"Ted" <no@thanks.invalid> wrote in message news:200311166112.081904@dev...
Here's what I'm trying to do:

I have a table, which I don't have administration over, with 4 interesting
fields:
RecordCount - an auto incrementing primary key
CardNumber - integer
CompanyID - integer
AccessPriv - an string that varies constantly

There could be identical CardNumber's, but they must have different
CompanyId's. A data set might look like this:

RecordCount | CardNumber | CompanyID | AccessPriv
1 | 1 | 82 | all
2 | 2 | 82 | level 1
3 | 2 | 84 | all
4 | 1 | 82 | none

The table is transactional, so old records will not be flushed even though
new records contain the most current data (records 1 and 4 in this case).
I'm wondering if there's a way to do a SQL SELECT query that, as it goes
from the beginning to the end of the table, overwrites previous records when
later CardNumber's and CompanyID's match the previous records. So in this
case, the query would only return rows 2-4 because record 4's CardNumber and
CompanyID match record 1's.

I know this is possible with application logic, but is it possible with an
SQL query?

Thanks.

Thanks for the help David and Igor!

-Ted


On Sun, 16 Nov 2003 10:08:57 -0800, Igor Raytsin wrote:
>*You could try this:
>
>*SELECT *
>*FROM _table_ t1
>*INNER JOIN (
>*SELECT MAX(RecordCount) as LastRecordCount FROM _table_
>*GROUP BY CardNumber, CompanyID ) as t2 ON t1.RecordCount =
>*t2.LastRecordCount
>
>
>*Hope this helps.
>*Igor Raytsin
>
>
>*"Ted" <no@thanks.invalid>*wrote in messagenews:200311166112.081904@dev...
>*Here's what I'm trying to do:
>
>*I have a table, which I don't have administration over, with 4interesting
>*fields:
>*RecordCount - an auto incrementing primary key
>*CardNumber - integer
>*CompanyID - integer
>*AccessPriv - an string that varies constantly
>
>*There could be identical CardNumber's, but they must have different
>*CompanyId's. *A data set might look like this:
>
>*RecordCount | CardNumber | CompanyID | AccessPriv
>*1 | 1 | 82 | all
>*2 | 2 | 82 | level 1
>*3 | 2 | 84 | all
>*4 | 1 | 82 | none
>
>*The table is transactional, so old records will not be flushed eventhough
>*new records contain the most current data (records 1 and 4 in this case).
>*I'm wondering if there's a way to do a SQL SELECT query that, as it goes
>*from the beginning to the end of the table, overwrites previous recordswhen
>*later CardNumber's and CompanyID's match the previous records. *So inthis
>*case, the query would only return rows 2-4 because record 4's CardNumberand
>*CompanyID match record 1's.
>
>*I know this is possible with application logic, but is it possible withan
>*SQL query?
>
>*Thanks.

FWIW...

SELECT *
FROM _table_ t
where RecordCount in (
select MAX(RecordCount)
FROM _table_ t1
GROUP BY CardNumber, CompanyID) as t2

HTH

Steve

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

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