I have a SQL Server table
AccountAction which is denormalised. It is a flattened version of the
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:
However I'm having some trouble getting the information back for a restricted subset in a simple stored procedure.
select Account, Action
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.
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.
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.
SELECT * FROM AccountAction WHERE account IN (SELECT account FROM AccountAction GROUP BY account HAVING account BETWEEN *start-account* AND *end-account* ORDER BY account )
Explanation: The subquery groups by the distinct accounts (and allows for more fine-grained selection criteria than a simple
DISTINCT) and returns only those accounts. The outer SELECT gets you a variable number of rows depending on the distinct accounts fetched by the subquery.
EDIT: The above assumes that one can filter by the
account field in
AccountAction table; this is usually the case in tables that join a
M:N relationship at DB level.
If I got the question right then if u want to select initial 10 rows then use
SELECT TOP 10 Account, Action FROM AccountAction
or if u want some initial 20 percent records then use-
SELECT TOP 10 PERCENT Account, Action FROM AccountAction
did you try TOP?
declare @hoW_many int set @hoW_many = 10 select top (@hoW_many) * from AccountAction
A simple sub-select with
top keyword (and distinct), would give you all actions for first X accounts
select * from AccountAction where Account in (select distinct top (@NumberOfAccounts) Account from AccountAction order by Account)