Solved

Limititing number of rows returned

Posted on 2002-06-14
10
382 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Paddyo
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 2

Expert Comment

by:mmcmillen
ID: 7078209
what will tell you the records are the "last" for this account number?  
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7078210
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)
0
 
LVL 1

Expert Comment

by:johan_brohn
ID: 7078233
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:mmcmillen
ID: 7078252
what will tell you the records are the "last" for this account number?  
0
 

Author Comment

by:Paddyo
ID: 7078266
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.




0
 
LVL 1

Expert Comment

by:johan_brohn
ID: 7078293
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 7085796
Hi,

What about

Select top 3 *
from myTableA
where Account = <account in question>
order by Account asc, LastSavedDate desc

Regards
  David

0
 

Author Comment

by:Paddyo
ID: 7087791
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.
0
 
LVL 1

Accepted Solution

by:
johan_brohn earned 300 total points
ID: 7089133
>> I am only reading from one table yet you are
suggesting that I compare and sort fields from 2 tables.

No, one table to alias. The table is "myTable" and the alias are "A" and "B".

>> I do understand the IN (0,1,2) element of the
statement.

Example:

myTable
id, account
-------
1,'a'
2,'a'
3,'a'
4,'a'
5,'a'
6,'b'
7,'c'
8,'c'
9,'c'

Result:
id, account
-------
3,'a' there exists 2 with higher id for 'a'
4,'a' there exists 1 with higher id for 'a'
5,'a' there exists 0 with higher id for 'a'
6,'b' there exists 0 with higher id for 'b'
7,'c' there exists 2 with higher id for 'c'
8,'c' there exists 1 with higher id for 'c'
9,'c' there exists 0 with higher id for 'c'

Not within the result:
1,'a' there exists 4 with higher id for 'a'
2,'a' there exists 3 with higher id for 'a'

--get rows from myTable
SELECT *
FROM myTable A
--for each row, count the numbers of rows with a higher
--id but the very same account "B.id > A.id".
--if there only exists 0, 1 or 2 "IN (0,1,2)" records with higher id for the very same account then get it, otherwise just ignore it.
WHERE (SELECT Count(*) FROM myTable B WHERE B.id > A.id AND A.Account = B.Account) IN (0,1,2)
ORDER BY Account, id

0
 

Author Comment

by:Paddyo
ID: 7089561
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.NET Application Installation with sqlserver 8 32
Need help in debugging a UDF results 7 27
SQL Recursion schedule 13 19
MS SQL + date 6 23
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question