Reza Reza - 4 months ago 7
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

Try this

WITH cte
     AS (SELECT *,
                Row_number()
                  OVER (
                    ORDER BY id) AS rowid
         FROM   <tablename>)
SELECT *
FROM   cte
WHERE  EXISTS(SELECT 1
              FROM   cte
              WHERE  rowid >= 5)