Reza Reza - 1 year ago 73
SQL Question

Return the tuple in sql query result if it occures more than a predefine number (like 5).

I have a lots of lots of rows made by joining different tables. I would like to write a query that it returns the current rows if and only if the number of rows bigger than a specific number like 5 otherwise returns nothing. I know that I can do it pragmatically but is there any way to do it in sql server as a query.

EDIT: I chose the way that @prdp suggested. created a common table expression with row_number and select all that have rowId bigger than 5.

Answer Source

Try this

WITH cte
     AS (SELECT *,
                  OVER (
                    ORDER BY id) AS rowid
         FROM   <tablename>)
FROM   cte
              FROM   cte
              WHERE  rowid >= 5) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download