Group by column over partition ordered by date

Question

Post reply

Group by column over partition ordered by date

diegodeveloper

SSC Enthusiast

Points: 174

July 5, 2022 at 10:27 pm

Go to Answer

#4059293

Hi Everyone.

I need a script to get the first and second null value from some records grouped by a column and ordered by date.

sqlScenario

I need the 311888 and 123477 itinerary groups because they have the first and second LegSegment as null value ordered by CreationDate, something like:

select * from Test group by Itinerary order by CreationDate where (first LegSegment and secondLegSegment are null ordered by CreationDate)

This is a query example to build the scenario:

CREATE TABLE [dbo].[Test](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Itinerary] [int] NULL,
[LegSegment] [int] NULL,
[CreationDate] [datetime] NULL,
CONSTRAINT [PK_Test2] PRIMARY KEY CLUSTERED
(
[Id] ASC
))

INSERT INTO [dbo].[Test]
([Itinerary]
,[LegSegment]
,[CreationDate])
VALUES (311888,NULL,'2022-05-20 12:43:34.090'),(311888,NULL,'2022-05-21 12:43:34.090'), (311888,817430,'2022-05-22 12:43:34.090')
, (271888,NULL,'2022-05-20 12:43:34.090'),(271888,256321,'2022-05-21 12:43:34.090'), (271888,NULL,'2022-05-22 12:43:34.090')
, (312288,NULL,'2022-05-20 12:43:34.090'),(312288,345689,'2022-05-21 12:43:34.090'), (312288,817430,'2022-05-22 12:43:34.090')
, (123456,345678,'2022-05-20 12:43:34.090'),(123456,344657,'2022-05-21 12:43:34.090'), (123456,817430,'2022-05-22 12:43:34.090')
, (777555,NULL,'2022-05-20 12:43:34.090'),(777555,555778,'2022-05-21 12:43:34.090'), (777555,456345,'2022-05-22 12:43:34.090')
, (123477,NULL,'2022-05-20 12:43:34.090'),(123477,NULL,'2022-05-21 12:43:34.090'), (123477,567897,'2022-05-22 12:43:34.090')
, (123477,567846,'2022-05-23 12:43:34.090')
GO

Thanks in advance.

ScottPletcher

SSC Guru

Points: 101098

July 5, 2022 at 11:48 pm

Answer

#4059334

;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Itinerary ORDER BY CreationDate) AS row_num
    FROM dbo.Test
)
SELECT T.*
FROM dbo.Test T
INNER JOIN (
    SELECT Itinerary
    FROM cte_add_row_num carn
    WHERE row_num IN (1, 2) AND LegSegment IS NULL
    GROUP BY Itinerary
    HAVING COUNT(*) = 2
) AS carn ON carn.Itinerary = T.Itinerary
ORDER BY T.Itinerary, T.CreationDate

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

You could try something like this

with rn_cte as (
    select *, case when row_number() over (partition by [Itinerary] order by [CreationDate])=1 and LegSegment is null then 1
                   when row_number() over (partition by [Itinerary] order by [CreationDate])=2 and LegSegment is null then 1
              else 0 end first_two
    from #Test)
select [Itinerary] 
from rn_cte
group by [Itinerary]
having sum(first_two)=2
order by [Itinerary];

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

I'm trying to think of a more efficient way, but for now this should at least function correctly:

;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Itinerary ORDER BY CreationDate) AS row_num
    FROM dbo.Test
)
SELECT T.*
FROM dbo.Test T
INNER JOIN cte_add_row_num carn1 ON carn1.Itinerary = T.Itinerary AND carn1.row_num = 1 AND carn1.LegSegment IS NULL
INNER JOIN cte_add_row_num carn2 ON carn2.Itinerary = T.Itinerary AND carn2.row_num = 2 AND carn2.LegSegment IS NULL
ORDER BY T.Itinerary, T.CreationDate

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ed B Hall of Fame Points: 3666 More actions · Answer 3

By first and second do you mean according to CreationDate? ID looks like it would also serve as an ordering column, but I did not want to make that assumption.

What output do you need? If just the Itinerary values, does this work? If you need the ID's then another approach will be needed.

SELECT a.Itinerary
FROM  (
        SELECT Itinerary, LegSegment, 
               ROW_NUMBER() OVER (PARTITION BY Itinerary ORDER BY CreationDate) AS RowNum
        FROM dbo.test
       ) AS a
WHERE  a.LegSegment IS NULL
AND    a.RowNum < 3
GROUP  BY a.Itinerary
HAVING COUNT(a.Itinerary) = 2;

I couldn't think of a good way to avoid using a rownumber, but I am interested to see alternatives.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

As I see it, it's not the ROW_NUMBER itself that causes inefficiency (SQL is remarkably efficient at providing ROW_NUMBER), it's the multiple uses of it and thus multiple scans of the table.

I believe the OP wanted to list all columns for all rows where the Itinerary met the conditions, so I would think at least two table scans would be required, but I'm trying to think of a (reasonable) way to get rid of the third one.

IF there are a lot of rows in the table, that could make a big difference. If not, maybe not worth worrying about.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

diegodeveloper SSC Enthusiast Points: 174 More actions · Answer 5

I have a big database where I have to implement this query, I run these scripts and all the solutions worked like a charm except #4059312. You are really skilled.

Thank you all of you 🙂

These are the final scripts with the original tables from the real database, all of them work

with rn_cte as (
    select *, case when row_number() over (partition by tripItineraryMasterID order by CreationDtSrv)=1 and prevLegSegmentsID is null then 1
                   when row_number() over (partition by tripItineraryMasterID order by CreationDtSrv)=2 and prevLegSegmentsID is null then 1
              else 0 end first_two
    from tripLegSegments)
select tripItineraryMasterID 
from rn_cte
group by tripItineraryMasterID
having sum(first_two)=2
order by tripItineraryMasterID;

SELECT a.tripItineraryMasterID
FROM  (
        SELECT tripItineraryMasterID, prevLegSegmentsID, 
               ROW_NUMBER() OVER (PARTITION BY tripItineraryMasterID ORDER BY CreationDtSrv) AS RowNum
        FROM tripLegSegments
       ) AS a
WHERE  a.prevLegSegmentsID IS NULL
AND    a.RowNum < 3
GROUP  BY a.tripItineraryMasterID
HAVING COUNT(a.tripItineraryMasterID) = 2;

WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY tripItineraryMasterID ORDER BY CreationDtSrv) AS row_num
    FROM tripLegSegments
)
SELECT T.*
FROM tripLegSegments T
INNER JOIN (
    SELECT tripItineraryMasterID
    FROM cte_add_row_num carn
    WHERE row_num IN (1, 2) AND prevLegSegmentsID IS NULL
    GROUP BY tripItineraryMasterID
    HAVING COUNT(*) = 2
) AS carn ON carn.tripItineraryMasterID = T.tripItineraryMasterID
ORDER BY T.tripItineraryMasterID, T.CreationDtSrv