Paddyo
asked on
Limititing number of rows returned
Hi
I am trying to achieve the following
I have a table with many records. Each record has an account number field.
I want to get the last 3 records entered for each of the accounts into a recordset.
Is there an sql select query which might achieve this.
thanks in advance
I am trying to achieve the following
I have a table with many records. Each record has an account number field.
I want to get the last 3 records entered for each of the accounts into a recordset.
Is there an sql select query which might achieve this.
thanks in advance
what will tell you the records are the "last" for this account number?
Yes this is possible I need some more info about the table. Can you send me the layout. Is there an Identity field, if so this would work:
Select Distinct *
FROM Table T2
WHERE ID IN
(Select TOP 3 ID
from table T1
Where T1.AccountNum = T2.AccountNum
Order By ID Desc)
Select Distinct *
FROM Table T2
WHERE ID IN
(Select TOP 3 ID
from table T1
Where T1.AccountNum = T2.AccountNum
Order By ID Desc)
Replace the myTable with the real table name
and replace SortColumn with the real column to sort on.
SELECT *
FROM myTable A
WHERE (SELECT Count(*) FROM myTable B WHERE B.SortColumn < A.SortColumn AND A.Account = B.Account) IN (1,2,3)
ORDER BY Account, SortColumn
and replace SortColumn with the real column to sort on.
SELECT *
FROM myTable A
WHERE (SELECT Count(*) FROM myTable B WHERE B.SortColumn < A.SortColumn AND A.Account = B.Account) IN (1,2,3)
ORDER BY Account, SortColumn
what will tell you the records are the "last" for this account number?
ASKER
mmcmillen
I have an identity field.
__________________-
I am entering a range of accounts on a from
i.e. txtLwrAccCode
txtUprAccCode
I then want to select the last ??? number of records for each of the account codes in the range.
The last ??? number will be entered at run time by the user.
I have an identity field.
__________________-
I am entering a range of accounts on a from
i.e. txtLwrAccCode
txtUprAccCode
I then want to select the last ??? number of records for each of the account codes in the range.
The last ??? number will be entered at run time by the user.
Sorry
To get the three last records (sorted by sortColumn) for each account code.
SELECT *
FROM myTable A
WHERE (SELECT Count(*) FROM myTable B WHERE B.SortColumn > A.SortColumn AND A.Account = B.Account) IN
(0,1,2)
ORDER BY Account, SortColumn
To get the three last records (sorted by sortColumn) for each account code.
SELECT *
FROM myTable A
WHERE (SELECT Count(*) FROM myTable B WHERE B.SortColumn > A.SortColumn AND A.Account = B.Account) IN
(0,1,2)
ORDER BY Account, SortColumn
Hi,
What about
Select top 3 *
from myTableA
where Account = <account in question>
order by Account asc, LastSavedDate desc
Regards
David
What about
Select top 3 *
from myTableA
where Account = <account in question>
order by Account asc, LastSavedDate desc
Regards
David
ASKER
Thanks for all the suggestions
I have tried some of them without achieving the required results.
Johan Brohn:
I dont fully understand what to do with your suggestion. I am only reading from one table yet you are suggesting that I compare and sort fields from 2 tables. I do understand the IN (0,1,2) element of the statement.
dtodd:
I need the the last few records for several accounts. You suggestion would work perfectly for one account
Thanks again for the suggestions.
I have tried some of them without achieving the required results.
Johan Brohn:
I dont fully understand what to do with your suggestion. I am only reading from one table yet you are suggesting that I compare and sort fields from 2 tables. I do understand the IN (0,1,2) element of the statement.
dtodd:
I need the the last few records for several accounts. You suggestion would work perfectly for one account
Thanks again for the suggestions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much Johan Brohn
I think its obvious that I am not the worlds greatest expert on SQL but I am learning and with help such as I have received I will quickly become competent.
Thank you again.
I think its obvious that I am not the worlds greatest expert on SQL but I am learning and with help such as I have received I will quickly become competent.
Thank you again.