CODEWITHSUNDEEP
sql-servert-sql
user3463579
user3463579

Reputation: 105

Select rowset with null value in first row of group by result set

I am stuck with a problem.

I have some data likes these :

Id  Creation date       Creation date hour range    Id vehicule Id variable Value
1   2017-03-01 9:10     2017-03-01 9:00             1           6           0.18
2   2017-03-01 9:50     2017-03-01 9:00             1           3           0.50
3   2017-03-01 9:27     2017-03-01 9:00             1           3           null
4   2017-03-01 10:05    2017-03-01 10:00            1           3           0.35
5   2017-03-01 10:17    2017-03-01 10:00            1           3           0.12
6   2017-03-01 9:05     2017-03-01 9:00             1           5           0.04
7   2017-03-01 9:57     2017-03-01 9:00             1           5           null

I need to select rowset group by Id vehicule, Id variable, Creation date hour range and order by group by Id vehicule, Id variable, Creation date where the first Value is null but second value, third value, ... is not null. So, in the sample above, the following rowset :

Id  Creation date       Creation date hour range    Id vehicule Id variable Value
3   2017-03-01 9:27     2017-03-01 9:00             1           3           null
2   2017-03-01 9:50     2017-03-01 9:00             1           3           0.50

Could you help me please ?

Thank you

Upvotes: 0

Views: 1105

Answers (5)

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

For 'where the first Value is null but second value, third value, ... is not null' i suppose you want to filter cases where there is a null and a not null value at [Value] within the set you group by, to decide to filter or not that grouped row. This cannot be filtered on standard WHERE clause because at WHERE clause each row is filtered with conditions relevant to that row scope only. Simply put, each row filtered cannot 'see' other rows unless you use sub-query. You need to use HAVING clause (the comment out is for 2+ null records) This will work:

> DECLARE @mytbl TABLE(Id INT, [Creation date] DATETIME, [Creation date
> hour range] DATETIME, [Id veh] INT, [Id var] INT, Value INT )
> 
> INSERT INTO @mytbl VALUES (1,'2017-03-01 9:10 ','2017-03-01 9:00 ',1,
> 6, 0.18) INSERT INTO @mytbl VALUES (2,'2017-03-01 9:50 ','2017-03-01
> 9:00 ',1, 3, 0.50) INSERT INTO @mytbl VALUES (3,'2017-03-01 9:27
> ','2017-03-01 9:00 ',1, 3, NULL) INSERT INTO @mytbl VALUES
> (4,'2017-03-01 10:05','2017-03-01 10:00',1, 3, 0.35) INSERT INTO
> @mytbl VALUES (5,'2017-03-01 10:17','2017-03-01 10:00',1, 3, 0.12)
> INSERT INTO @mytbl VALUES (6,'2017-03-01 9:05 ','2017-03-01 9:00 ',1,
> 5, 0.04) INSERT INTO @mytbl VALUES (7,'2017-03-01 9:57 ','2017-03-01
> 9:00 ',1, 5, NULL)
> 
> SELECT [Id veh], [Id var],[Creation date hour range] FROM @mytbl GROUP
> BY [Id veh], [Id var],[Creation date hour range] HAVING COUNT([Id
> veh]) - COUNT(Value) = 1
> --HAVING COUNT([Id veh]) - COUNT(Value) >= 1 ORDER BY [Id veh], [Id var],[Creation date hour range]

Upvotes: 0

user7715598
user7715598

Reputation:

Try this Query

DECLARE @Nulloccurrence INT=1 -- Give like 1,2,3 value to get first null occurrence 2 for 2nd null occurrence 

SELECT TOP 2 *
FROM cte
WHERE Id <= (
SELECT ID FROM
(
        SELECT  Id, ROW_NUMBER()OVER( Order by id) AS Seq   
        FROM cte
        WHERE (
                CASE 
                    WHEN CAST(variableValue AS VARCHAR) IS NULL
                        THEN 'P'
                    ELSE CAST(variableValue AS VARCHAR)
                    END
                ) = 'P'
                )Dt
                WHERE Dt.Seq=@Nulloccurrence
        )
ORDER BY 1 DESC

Expected Result

Id  Creationdate    Creationdatehourrange   Ids vehicleId   variableValue
------------------------------------------------------------------------
3   2017-03-01 9:27  2017-03-01 9:00            1       3           NULL
2   2017-03-01 9:50  2017-03-01 9:00            1       3           0.50

Upvotes: 0

Kirky1
Kirky1

Reputation: 68

indeed group by was no use here. Also I wasn't sure where your 10:00 records were going to. Does this help?

;WITH CTE_ADD_SOME_LOGIC
AS
(
SELECT Id, CreationDate         ,CreationDateHourRange      ,IdVehicle      ,IdVariable ,Value
, CASE WHEN Value IS NULL THEN 1 ELSE 0 END AS VALUE_IS_NULL FROM tbl

),
CTE_MORE_LOGIC
AS
(
SELECT Id, CreationDate         ,CreationDateHourRange      ,IdVehicle      ,IdVariable ,Value,VALUE_IS_NULL
, RANK() OVER (ORDER BY CreationDateHourRange,VALUE_IS_NULL) AS RN FROM CTE_ADD_SOME_LOGIC),
CTE_ORDER
AS
(
SELECT Id, CreationDate         ,CreationDateHourRange      ,IdVehicle      ,IdVariable ,Value,VALUE_IS_NULL, RN
, ROW_NUMBER() OVER(PARTITION BY RN ORDER BY RN,IdVehicle,IdVariable,CreationDate, VALUE_IS_NULL DESC) AS HIERARCHY  FROM CTE_MORE_LOGIC
)

SELECT Id, CreationDate         ,CreationDateHourRange      ,IdVehicle      ,IdVariable ,Value FROM CTE_ORDER WHERE HIERARCHY = 1
ORDER BY Id

Upvotes: 0

yukiyamafuyu
yukiyamafuyu

Reputation: 11

You will have no luck with a group by in this case. I would give 2 "if exists" into the where clause to filter all IDs that fit your criteria:

(for example/not tested/probably takes forever)

select *
from yourTable y1
where id in

--the id must be in all IDs, where the first value of the set is null

--same ID instead of group by
(select 1 from yourTable y2 where y1.IDs = y2.IDs and  
--the first in the set
y2.createdate = (select min(createdate) from yourtable y3 with sameid) and
y2.value is null)

AND

--the id must also be in the IDs, where there are values besides the first that are not null

id in (same select but with "not min" and "not null" obviously

hope that helped :)

Upvotes: 1

Matt W
Matt W

Reputation: 12423

Include the Value field in the ORDER BY clause and it will be sorted to the top because NULL has a lower practical value than a non-NULL value.

Assuming (because your middle paragraph is hard to understand) you want all the fields output but you want the 4th and 5th columns to produce some grouping of the output, with Value = NULL at the top of each group:

SELECT Id, CreatedDate, CreatedDateHourRange, IdVehicule, IdVariable, Value
ORDER BY IdVehicule, IdVariable, Value

I don't see any need for an actual GROUP BY clause.

I think it is unclear as to whether you want to limit the NULL Value rows in each block to just one row of NULL, but if you do you would need to state the order for which the datetime columns are sorted.

Upvotes: 0

Related Questions