Row Number Group by Account

Question

Hi

  I have below Sql Query and i want to display  Row Number group by Account

SELECT
 A."Account" ,(A."Debit") AS "Debit", (A."Credit") AS "Credit",
From Masters A
order by A."Account"

SQL

Copy

Thanks

Answer 1

Hello Ramco,

To display a Row Number grouped by Account, use the ROW_NUMBER() window function with PARTITION BY.

SELECT 
    ROW_NUMBER() OVER (PARTITION BY A."Account" ORDER BY A."Account") AS "Row Number",
    A."Account", 
    A."Debit", 
    A."Credit"
FROM Masters A
ORDER BY A."Account", "Row Number";

Good Luck!

Answer 2

It appears that you are looking to incorporate row numbers grouped by the "Account" field in your SQL query. One common approach to achieve this is by using the ROW_NUMBER() window function in SQL. Here is how you can modify your query to include row numbers grouped by the "Account":

SELECT
    A."Account",
    (A."Debit") AS "Debit",
    (A."Credit") AS "Credit",
    ROW_NUMBER() OVER(PARTITION BY A."Account" ORDER BY A."Account") AS RowNumber
FROM Masters A
ORDER BY A."Account";

In this adjusted query, the ROW_NUMBER() function is used alongside the PARTITION BY clause to assign a unique row number to each row within a specific "Account" group. The ORDER BY clause within the ROW_NUMBER function determines the order in which the row numbers are assigned.

By executing this query, you should be able to obtain a result set that includes a row number for each row within the distinct "Account" groups. This can be particularly useful for various analytical and reporting purposes. Feel free to test this query with your data and let me know if you have any further questions or need additional assistance.