Geek Answers Handbook

SQL to get X number of accounts from the database, which can be a variable number of rows

I have a SQL Server table AccountActionthat is being denormalized. This is a flattened version of the tables Accountand Actionwhich, I hope, should be much faster to send queries across millions of rows. One Accountcan have many Actions, so the table looks like this:

Account     Action
account1    action1
account1    action2
account1    action10
account2    action5

However, I had problems returning information for a limited subset in a simple stored procedure.

select Account, Action
from AccountAction
where ???

I am looking to get the first X accounts, with all their actions. Thus, it will be a dynamic number of rows. Therefore, using the example table above, if I passed in 1, I would get 3 rows (i.e., provided me with all the rows for the first account).

( , - )

ROWNUM ? , , .

TOP , 3 , " () ". , 3? . , , account1 action99 55 .

+5
5
WITH
  SequencedData
AS
(
  SELECT
    DENSE_RANK() OVER (ORDER BY Account) AS account_sequence_id,
    *
  FROM
    AccountAction
)
SELECT
  *
FROM
  SequenceData
WHERE
  account_sequence_id = ???

, ...

WHERE
  account_sequence_id BETWEEN 3 AND 5    -- For the 3rd, 4th and 5th accounts.
+4
SELECT *
FROM AccountAction
WHERE account IN  (SELECT account
    FROM AccountAction
    GROUP BY account HAVING account BETWEEN *start-account* AND *end-account*
    ORDER BY account
)

: ( , DISTINCT) . SELECT , .

EDIT: , account AccountAction; , M:N DB.

+1

, u 10 ,

SELECT TOP 10 Account, Action
FROM AccountAction

20- , -

SELECT TOP 10 PERCENT Account, Action
FROM AccountAction
0

TOP?

 declare @hoW_many int
 set @hoW_many = 10

 select top (@hoW_many)  * 
 from AccountAction
0

top ( ) X

select * from AccountAction
where Account in 
(select distinct top (@NumberOfAccounts) Account 
from AccountAction order by Account)
0

More articles:


All Articles