473,322 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Is it possible to return last row out of multiple based on row index?

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

Jul 20 '05 #1
4 5907
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
--
Jul 20 '05 #2
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.

Jul 20 '05 #3
Ted
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.

Jul 20 '05 #4
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!
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

40
by: Ron Adam | last post by:
After considering several alternatives and trying out a few ideas with a modified list object Bengt Richter posted, (Thank You), I think I've found a way to make slice operation (especially far end...
6
by: Terentius Neo | last post by:
Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a select statement? I mean something like this: Select c.number, call procedure( c.number ) as list from table c With best...
0
by: etamp | last post by:
Etamp.net, the fast rss-reader for RSS content including weblogs and news, the fast search engine for RSS content including weblogs and news, No software download, All OS' & Browsers, IE, Netscape,...
11
by: Tim Frawley | last post by:
I need to return a DataRow or the Row Index in a DataSet wherein the value I am attempting to find is not a primary key. I have to do this often, more than 200 times when importing a file so it...
12
by: Peter Proost | last post by:
Hi group, has anyone got any suggestions fot the best way to handle this problem, I've got 3 tables for example table A, B, and C table A looks like name, value table B looks like name, value...
9
by: MSDNAndi | last post by:
Hi, I have a set of simple webservices calls that worked fine using .NET Framework 1.0. I am calling a Java/Apache based webservices, the calling side is not able to supply a proper WSDL. ...
11
by: Martin Höfling | last post by:
Hi there, is it possible to put the methods of a class in different files? I just want to order them and try to keep the files small. Regards Martin
0
by: Tarik Monem | last post by:
I have been working on an all AJAX/DOM web site which is set to go live today and I thought I'd share my discoveries with all of you whom have helped me when I have encountered different issues along...
6
by: tgnelson85 | last post by:
Hello, C question here (running on Linux, though there should be no platform specific code). After reading through a few examples, and following one in a book, for linked lists i thought i would...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.