SQL query to return n top records which have a null field or records grouped by that field

sql query to find null columns in a table
sql check if column is null or empty
how to select not null columns in sql server
sql select where multiple columns are not null
sql is null or empty
sql is null or empty in where clause
sql is not null or empty
is null sql

I have a Table A which has a filter column

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |
| 4  |  frank | null   |
| 5  |  sarah |  b     |
| 6  |  jamie |  b     |

Assume the records are ordered by id. Records with the same value for filter should be count as one only.

TOP(1) should return

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |

TOP(2) should return

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |

TOP(3) should return

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |
| 4  |  frank | null   |

TOP(4) should return

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |
| 4  |  frank | null   |
| 5  |  sarah |  b     |
| 6  |  jamie |  b     |

You can use a windowed MIN() to group values with the same filter (and NULL values on different groups), then use a DENSE_RANK() to flatten the values so it can be filtered later.

IF OBJECT_ID('tempdb..#Values') IS NOT NULL
    DROP TABLE #Values

CREATE TABLE #Values (
    ID INT IDENTITY,
    Name VARCHAR(10),
    Filter VARCHAR(10))

INSERT INTO #Values (
    Name,
    Filter)
VALUES
    ('joe', 'a'),
    ('anna', 'a'),
    ('mike', NULL),
    ('frank', NULL),
    ('sarah', 'b'),
    ('jamie', 'b'),
    ('john', 'a')

DECLARE @v_TopFilter INT = 4 -- Your top filter here

;WITH MinimumByFilter AS
(
    SELECT
        V.*,
        MinimumIDByFilter = MIN(V.ID) OVER (
            PARTITION BY 
                V.Filter,
                CASE WHEN V.Filter IS NULL THEN V.ID END)
    FROM
        #Values AS V
),
DenseRank AS
(
    SELECT
        M.*,
        DenseRank = DENSE_RANK() OVER(ORDER BY M.MinimumIDByFilter ASC)
    FROM
        MinimumByFilter AS M
)
SELECT
    D.ID,
    D.Name,
    D.Filter
FROM
    DenseRank AS D
WHERE
    D.DenseRank <= @v_TopFilter
ORDER BY
    D.ID ASC

You can check what the functions return here:

ID  Name    Filter  MinimumIDByFilter   DenseRank
1   joe     a       1                   1
2   anna    a       1                   1
7   john    a       1                   1
3   mike    NULL    3                   2
4   frank   NULL    4                   3
5   sarah   b       5                   4
6   jamie   b       5                   4

If you don't need the information from a field, don't return it in the query. When you need to return a count of the records returned by an SQL statement, Count​(*) counts records that contain null fields; Count([FieldName]) checks for nulls and necessary. n When you need to group records by the values of a field used in  In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. For more details, check out Wikipedia's explanation of NULL in SQL . We will use the following employee table to illustrate how the GROUP BY clause works with NULL values.

On second thought, you are trying to select first n distinct filters. Just find the smallest id per filter and number them:

DECLARE @A TABLE(id INT, name VARCHAR(100), filter VARCHAR(100));
INSERT INTO @A VALUES
(1, 'joe',     'y' ), -- 1st
(2, 'anna',    'x' ), -- 2nd
(3, 'mike',    NULL), -- 3rd
(4, 'frank',   NULL), -- 4th
(5, 'sarah',   'x' ),
(6, 'jamie',   'y' ),
(9, 'forrest', 'z' ); -- 5th

WITH filter_minid AS (
    SELECT filter, MIN(id) AS minid
    FROM @A
    GROUP BY filter, CASE WHEN filter IS NULL THEN id END
), filter_minid_number AS (
    SELECT filter, minid, ROW_NUMBER() OVER (ORDER BY minid) AS rn
    FROM filter_minid
)
SELECT *
FROM @A a 
INNER JOIN filter_minid_number ON a.filter = filter_minid_number.filter OR a.id = filter_minid_number.minid
WHERE rn <= 5 -- this is where you filter for n distinct ids

Result:

| id | name    | filter | filter | minid | rn |
|----|---------|--------|--------|-------|----|
| 1  | joe     | y      | y      | 1     | 1  |
| 2  | anna    | x      | x      | 2     | 2  |
| 3  | mike    | NULL   | NULL   | 3     | 3  |
| 4  | frank   | NULL   | NULL   | 4     | 4  |
| 5  | sarah   | x      | x      | 2     | 2  |
| 6  | jamie   | y      | y      | 1     | 1  |
| 9  | forrest | z      | z      | 9     | 5  |

When you need to return a count of the records returned by an SQL statement records that contain null fields; Count([FieldName]) checks for nulls and You should use calculated fields only in top-level queries, and even then, only when necessary. ✦ When you need to group records by the values of a field used in a join,  Similarly, Beowulf (id 6) – the famous Old English epic poem – has no known author, so primary_author is NULL. In this case, we may want to query for results containing only the first four records, thereby excluding the final two records which have NULL values in either primary_author or published_date.

You can try this.

DECLARE @Tbl TABLE ( id INT,  name  varchar(10), filter varchar(10))

INSERT INTO @Tbl VALUES
(1 ,'joe', 'a'),
(2 ,'anna', 'a'),
(3 ,'mike', null),
(4 ,'frank', null),
(5 ,'sarah', 'b'),
(6 ,'jamie', 'b')

DECLARE @TOP INT = 3

SELECT id, name, filter FROM 
    ( SELECT *, DENSE_RANK() OVER(ORDER BY SUB_RNK) RNK
        FROM ( SELECT *, 
            MIN(id) OVER(PARTITION BY ISNULL(filter,id) ) SUB_RNK
          FROM @Tbl ) T1
    ) T2
WHERE 
    T2.RNK <= @TOP

Result: (for top 3)

id          name       filter
----------- ---------- ----------
1           joe        a
2           anna       a
3           mike       NULL
4           frank      NULL

Use the MySQL engine to only grab records that you desire while excluding those While most applications will have some form of server-side or even client​-side NULL or empty values, using another language and that additional burden of but it's good practice to enclose grouped comparators for better readability. This function has been around since SQL Server 2005 and at its core, provides a way to provide sequential numbering for rows returned by a query. One of the requirements for an export process was to return the most recent two customer orders for each customer account and outputting this to CSV file.

using subquery

CREATE PROCEDURE `top` (IN x INT UNSIGNED)
BEGIN
select * from tableA where `filter` in (select distinct `filter` from tableA LIMIT x )
END

using join

CREATE PROCEDURE `top` (IN x INT UNSIGNED)
    BEGIN
    select * from tableA A 
      join (select distinct `filter` from tableA LIMIT x ) AA
      on A.`filter` = AA.`filter`
END

how to use the GROUP BY clause but how does SQL's GROUP BY Let's start by executing a simple SQL query with both the GROUP BY clause and NULL values: COUNT(salary) returns the number of records that have non-NULL table, even those records with NULL values in some or all fields. and message null. and message = ' '. and message like ''. and message = ''. >. Using an AND condition means that BOTH conditions must be true for rows to be returned. You don't show what the first condition, the one before the AND, is. And these tests will not return rows where MESSAGE is null.

You can use dense_rank():

select t.*
from (select t.*,
             dense_rank() over (order by filter,
                                         (case when filter is null then id end)
                                end) as seqnum
      from t
     ) t
where seqnum < ?  -- whatever your limit is;

If you wanted to use top for this, you can use top with ties:

select top (?) with ties t.*
from (select t.*,
             dense_rank() over (order by filter,
                                         (case when filter is null then id end)
                                end) as seqnum
      from t
     ) t
order by seqnum;

A field with a NULL value is one that has been left blank during record creation! How to Test for NULL Values? It is not possible to test for NULL values with  For some reason I can never remember how to search for database table fields that are either NULL or NOT NULL.I always try to use the = operator or something else.. So, for myself, here’s an example of how to perform a SQL query and find all records where a field in a database table is NULL:

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be  Well by definition you can't return anything if there are no records. 😉 You would have to force the query to always return a resultset. Something like this: select top 1 name from

Group by is one of the most frequently used SQL clauses. It allows you to collapse a field into its distinct values. Using having, you can return the aggregate filtered results! A query select statement can have a column name changed and continue to run, select null = null -- returns null, not True. When you run a query such as the ones above, it should be noted that the subquery runs first. The subquery in the EXISTS and NOT EXISTS statements is the query that returns order records. This query runs first. Then, the main or “outer” query runs. In the above examples, the outer query is the select statement based on the customers table.

This tutorial shows you how to use the SQL Server SELECT TOP statement to limit the number of rows or percentage of rows returned by a In this syntax, the SELECT statement can have other clauses such as WHERE , JOIN , HAVING , and GROUP BY . CHECK Constraint · UNIQUE Constraint · NOT NULL Constraint  Find Free Sql Databases. Check out 1000+ Results from Across the Web

Comments
  • Edit the question with your desired result.
  • This question is very unclear. It might be better to explain what requirement you're trying to meet (i.e. why you want the results you do) in addition to what results you want. It doesn't make sense to always return more rows than the TOP(n) that you're asking for, and the sort order you want to apply isn't clear either.
  • assume the records are sorted by id. I want to be able to ask for the TOP(n) records but count the records with the same filter value as one only
  • If you add another row ID 7 with filter a, you want it displayed on TOP(1)?
  • yes, because when ordered by id, there is a filter a record in the first position, so I'm expected to return all the records with the filter a...