SQL to get X number of accounts from DB, which could be variable number of rows

sql, sql-server, stored-procedures

Solution

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

Or, for multiples...

WHERE
  account_sequence_id BETWEEN 3 AND 5    -- For the 3rd, 4th and 5th accounts.

Problem

I have a SQL Server table `AccountAction` which is denormalised. It is a flattened version of the `Account` and `Action` tables, which I'm hoping should be a lot quicker for reporting queries over millions of rows. One `Account` can have many `Actions`, so the table looks similar to: ``` Account Action account1 action1 account1 action2 account1 action10 account2 action5 ``` However I'm having some trouble getting the information back for a restricted subset in a simple stored procedure. ``` select Account, Action from AccountAction where ??? ``` What I'm looking for is to get the first X accounts, with all their actions. So this will be a dynamic number of rows. So using the example table above if I passed in 1, I would get 3 rows (i.e. give me all rows for the first account). (I don't mind that the account name will be in each row - it is pivoted elsewhere) Do I need to use a ROWNUM or similar to restrict the rows? I'm sure this must be a simpler issue than I've found so far. EDIT The answers using TOP won't work, in the example I'd be wanting 3 rows returned if I said 'give me one (the first) account'. But how do I know there will be 3? Its dynamic. Also they may not be sequential, what if account1's action99 was at position 55 million in the results.

Original source