How to collapse into a single row from multiple rowsSelect a single row from two distinct columnsCombine...

What is the Guild Die for?

How can a kingdom keep the secret of a missing monarchy from the public?

Are there any rules or guidelines about the order of saving throws?

Why does Python copy numpy arrays where the length of the dimensions are the same?

The totem pole can be grouped into

How to draw these kind of adjacent ovals with arrows in latex?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Why are recumbent bicycles and velomobiles illegal in UCI bicycle racing?

I hate taking lectures, can I still survive in academia?

Can a rabbi conduct a marriage if the bride is already pregnant from the groom?

Would Refreshing a Sandbox Wipe Out Certain Metadata?

How should I ship cards?

Why does the current not skip resistors R3 and R5 when R6 and R4 have no resistance?

Is it possible to detect 100% of SQLi with a simple regex?

Why are energy weapons seen as more acceptable in children's shows than guns that fire bullets?

Can you make a Spell Glyph of a spell that has the potential to target more than one creature?

Is a particular string regular (e.g is '010') regular?

Ethernet cable only works in certain positions

Discouraging missile alpha strikes

Can a planet be tidally unlocked?

Why does finding small effects in large studies indicate publication bias?

Diagram in Tikz environment

Why is Bernie Sanders maximum accepted donation on actblue $5600?

Why did Shae (falsely) implicate Sansa?



How to collapse into a single row from multiple rows


Select a single row from two distinct columnsCombine column from multiple rows into single rowCombine column from multiple rows into one row in SQLITEInserting rows into other table whilst preserving IDENTITYAggregate data from multiple rows into single rowHow to separate values from one column into two columns in SQL ServerCombining multiple-row data into a single rowGenerating two rows from one source rowHow 'Select * into' works into background?Display multiple rows of one table into one row without using PIVOT













0















Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest order_number.



Please share the tips to create a SQL Server query that can (a) transform data from the Source table to (b) data as shown in the Target table.



Data sample for the Source table:



enter image description here



Data sample for the Target table:



enter image description here










share|improve this question









New contributor




Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





















  • What version of SQL Server are you using? 2012, 2014, 2016, etc?

    – Scott Hodgin
    5 hours ago
















0















Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest order_number.



Please share the tips to create a SQL Server query that can (a) transform data from the Source table to (b) data as shown in the Target table.



Data sample for the Source table:



enter image description here



Data sample for the Target table:



enter image description here










share|improve this question









New contributor




Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





















  • What version of SQL Server are you using? 2012, 2014, 2016, etc?

    – Scott Hodgin
    5 hours ago














0












0








0








Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest order_number.



Please share the tips to create a SQL Server query that can (a) transform data from the Source table to (b) data as shown in the Target table.



Data sample for the Source table:



enter image description here



Data sample for the Target table:



enter image description here










share|improve this question









New contributor




Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest order_number.



Please share the tips to create a SQL Server query that can (a) transform data from the Source table to (b) data as shown in the Target table.



Data sample for the Source table:



enter image description here



Data sample for the Target table:



enter image description here







sql-server t-sql sql-server-2014 query






share|improve this question









New contributor




Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 3 hours ago









MDCCL

6,75731745




6,75731745






New contributor




Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 5 hours ago









RoxyRoxy

1




1




New contributor




Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Roxy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.













  • What version of SQL Server are you using? 2012, 2014, 2016, etc?

    – Scott Hodgin
    5 hours ago



















  • What version of SQL Server are you using? 2012, 2014, 2016, etc?

    – Scott Hodgin
    5 hours ago

















What version of SQL Server are you using? 2012, 2014, 2016, etc?

– Scott Hodgin
5 hours ago





What version of SQL Server are you using? 2012, 2014, 2016, etc?

– Scott Hodgin
5 hours ago










1 Answer
1






active

oldest

votes


















2














You should be able to use a common table expression along with the ROW_NUMBER and LEAD windowing functions to help with this problem.



--demo setup based on your sample data
Declare @T table(DIV VARCHAR(20), LS INT, TT VARCHAR(5), TN INT, LAT DECIMAL(7,3), LONG DECIMAL(7,3), [DATE] DATETIME, ORDER_NUM VARCHAR(30))

INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')

--the query
;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM @T
)
SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
FROM CTE
WHERE rn = 1




| div    | ls | tt | tn | lat     | long    | Latest_Test_Date        | Second_Latest_Dte       | ORDER_NUM  |
|--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 2019-02-13 15:22:00.000 | 1004104405 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 2019-02-10 14:22:00.000 | 1004104590 |




By selecting directly from the common table expresssion
SELECT * FROM CTE



| DIV    | LS | TT | TN | LAT     | LONG    | DATE                    | ORDER_NUM  | rn | prev                    |
|--------|----|----|----|---------|---------|-------------------------|------------|----|-------------------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 1004104405 | 1 | 2019-02-13 15:22:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-13 15:22:00.000 | 1004085755 | 2 | 2019-02-11 17:58:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-11 17:58:00.000 | 1004085730 | 3 | NULL |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 1004104590 | 1 | 2019-02-10 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-10 14:22:00.000 | 1004104520 | 2 | 2019-02-01 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-01 14:22:00.000 | 1004104405 | 3 | NULL |


you can see that the rn=1 rows are the latest row for each DIV. The LEAD window function looks to the row just before the latest row for that DIV to pick up the second_latest_date.





Update: based on your comment about wanting to union multiple tables before applying the windowing functions, you could use an inline table expression like this:



;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM
(
select cols from TableOne
UNION
select cols from TableTwo
) as t
)





share|improve this answer


























  • Thanks a lot scott. Solution you provided works correctly. However, I wanted to know, If i can use UNION within CTE two fetch data from two tables rather than one table and apply on top of it ? Like below query ?

    – Roxy
    4 hours ago






  • 1





    @Roxy - I added an example of how you might UNION before applying the windowing functions.

    – Scott Hodgin
    3 hours ago











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});






Roxy is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230160%2fhow-to-collapse-into-a-single-row-from-multiple-rows%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














You should be able to use a common table expression along with the ROW_NUMBER and LEAD windowing functions to help with this problem.



--demo setup based on your sample data
Declare @T table(DIV VARCHAR(20), LS INT, TT VARCHAR(5), TN INT, LAT DECIMAL(7,3), LONG DECIMAL(7,3), [DATE] DATETIME, ORDER_NUM VARCHAR(30))

INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')

--the query
;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM @T
)
SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
FROM CTE
WHERE rn = 1




| div    | ls | tt | tn | lat     | long    | Latest_Test_Date        | Second_Latest_Dte       | ORDER_NUM  |
|--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 2019-02-13 15:22:00.000 | 1004104405 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 2019-02-10 14:22:00.000 | 1004104590 |




By selecting directly from the common table expresssion
SELECT * FROM CTE



| DIV    | LS | TT | TN | LAT     | LONG    | DATE                    | ORDER_NUM  | rn | prev                    |
|--------|----|----|----|---------|---------|-------------------------|------------|----|-------------------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 1004104405 | 1 | 2019-02-13 15:22:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-13 15:22:00.000 | 1004085755 | 2 | 2019-02-11 17:58:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-11 17:58:00.000 | 1004085730 | 3 | NULL |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 1004104590 | 1 | 2019-02-10 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-10 14:22:00.000 | 1004104520 | 2 | 2019-02-01 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-01 14:22:00.000 | 1004104405 | 3 | NULL |


you can see that the rn=1 rows are the latest row for each DIV. The LEAD window function looks to the row just before the latest row for that DIV to pick up the second_latest_date.





Update: based on your comment about wanting to union multiple tables before applying the windowing functions, you could use an inline table expression like this:



;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM
(
select cols from TableOne
UNION
select cols from TableTwo
) as t
)





share|improve this answer


























  • Thanks a lot scott. Solution you provided works correctly. However, I wanted to know, If i can use UNION within CTE two fetch data from two tables rather than one table and apply on top of it ? Like below query ?

    – Roxy
    4 hours ago






  • 1





    @Roxy - I added an example of how you might UNION before applying the windowing functions.

    – Scott Hodgin
    3 hours ago
















2














You should be able to use a common table expression along with the ROW_NUMBER and LEAD windowing functions to help with this problem.



--demo setup based on your sample data
Declare @T table(DIV VARCHAR(20), LS INT, TT VARCHAR(5), TN INT, LAT DECIMAL(7,3), LONG DECIMAL(7,3), [DATE] DATETIME, ORDER_NUM VARCHAR(30))

INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')

--the query
;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM @T
)
SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
FROM CTE
WHERE rn = 1




| div    | ls | tt | tn | lat     | long    | Latest_Test_Date        | Second_Latest_Dte       | ORDER_NUM  |
|--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 2019-02-13 15:22:00.000 | 1004104405 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 2019-02-10 14:22:00.000 | 1004104590 |




By selecting directly from the common table expresssion
SELECT * FROM CTE



| DIV    | LS | TT | TN | LAT     | LONG    | DATE                    | ORDER_NUM  | rn | prev                    |
|--------|----|----|----|---------|---------|-------------------------|------------|----|-------------------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 1004104405 | 1 | 2019-02-13 15:22:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-13 15:22:00.000 | 1004085755 | 2 | 2019-02-11 17:58:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-11 17:58:00.000 | 1004085730 | 3 | NULL |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 1004104590 | 1 | 2019-02-10 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-10 14:22:00.000 | 1004104520 | 2 | 2019-02-01 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-01 14:22:00.000 | 1004104405 | 3 | NULL |


you can see that the rn=1 rows are the latest row for each DIV. The LEAD window function looks to the row just before the latest row for that DIV to pick up the second_latest_date.





Update: based on your comment about wanting to union multiple tables before applying the windowing functions, you could use an inline table expression like this:



;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM
(
select cols from TableOne
UNION
select cols from TableTwo
) as t
)





share|improve this answer


























  • Thanks a lot scott. Solution you provided works correctly. However, I wanted to know, If i can use UNION within CTE two fetch data from two tables rather than one table and apply on top of it ? Like below query ?

    – Roxy
    4 hours ago






  • 1





    @Roxy - I added an example of how you might UNION before applying the windowing functions.

    – Scott Hodgin
    3 hours ago














2












2








2







You should be able to use a common table expression along with the ROW_NUMBER and LEAD windowing functions to help with this problem.



--demo setup based on your sample data
Declare @T table(DIV VARCHAR(20), LS INT, TT VARCHAR(5), TN INT, LAT DECIMAL(7,3), LONG DECIMAL(7,3), [DATE] DATETIME, ORDER_NUM VARCHAR(30))

INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')

--the query
;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM @T
)
SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
FROM CTE
WHERE rn = 1




| div    | ls | tt | tn | lat     | long    | Latest_Test_Date        | Second_Latest_Dte       | ORDER_NUM  |
|--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 2019-02-13 15:22:00.000 | 1004104405 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 2019-02-10 14:22:00.000 | 1004104590 |




By selecting directly from the common table expresssion
SELECT * FROM CTE



| DIV    | LS | TT | TN | LAT     | LONG    | DATE                    | ORDER_NUM  | rn | prev                    |
|--------|----|----|----|---------|---------|-------------------------|------------|----|-------------------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 1004104405 | 1 | 2019-02-13 15:22:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-13 15:22:00.000 | 1004085755 | 2 | 2019-02-11 17:58:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-11 17:58:00.000 | 1004085730 | 3 | NULL |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 1004104590 | 1 | 2019-02-10 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-10 14:22:00.000 | 1004104520 | 2 | 2019-02-01 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-01 14:22:00.000 | 1004104405 | 3 | NULL |


you can see that the rn=1 rows are the latest row for each DIV. The LEAD window function looks to the row just before the latest row for that DIV to pick up the second_latest_date.





Update: based on your comment about wanting to union multiple tables before applying the windowing functions, you could use an inline table expression like this:



;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM
(
select cols from TableOne
UNION
select cols from TableTwo
) as t
)





share|improve this answer















You should be able to use a common table expression along with the ROW_NUMBER and LEAD windowing functions to help with this problem.



--demo setup based on your sample data
Declare @T table(DIV VARCHAR(20), LS INT, TT VARCHAR(5), TN INT, LAT DECIMAL(7,3), LONG DECIMAL(7,3), [DATE] DATETIME, ORDER_NUM VARCHAR(30))

INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')

--the query
;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM @T
)
SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
FROM CTE
WHERE rn = 1




| div    | ls | tt | tn | lat     | long    | Latest_Test_Date        | Second_Latest_Dte       | ORDER_NUM  |
|--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 2019-02-13 15:22:00.000 | 1004104405 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 2019-02-10 14:22:00.000 | 1004104590 |




By selecting directly from the common table expresssion
SELECT * FROM CTE



| DIV    | LS | TT | TN | LAT     | LONG    | DATE                    | ORDER_NUM  | rn | prev                    |
|--------|----|----|----|---------|---------|-------------------------|------------|----|-------------------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 1004104405 | 1 | 2019-02-13 15:22:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-13 15:22:00.000 | 1004085755 | 2 | 2019-02-11 17:58:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-11 17:58:00.000 | 1004085730 | 3 | NULL |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 1004104590 | 1 | 2019-02-10 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-10 14:22:00.000 | 1004104520 | 2 | 2019-02-01 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-01 14:22:00.000 | 1004104405 | 3 | NULL |


you can see that the rn=1 rows are the latest row for each DIV. The LEAD window function looks to the row just before the latest row for that DIV to pick up the second_latest_date.





Update: based on your comment about wanting to union multiple tables before applying the windowing functions, you could use an inline table expression like this:



;WITH CTE
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY DIV ORDER BY [DATE] DESC
) AS rn
,LEAD([date]) OVER (
PARTITION BY div ORDER BY [date] DESC
) AS prev
FROM
(
select cols from TableOne
UNION
select cols from TableTwo
) as t
)






share|improve this answer














share|improve this answer



share|improve this answer








edited 3 hours ago

























answered 4 hours ago









Scott HodginScott Hodgin

17.4k21534




17.4k21534













  • Thanks a lot scott. Solution you provided works correctly. However, I wanted to know, If i can use UNION within CTE two fetch data from two tables rather than one table and apply on top of it ? Like below query ?

    – Roxy
    4 hours ago






  • 1





    @Roxy - I added an example of how you might UNION before applying the windowing functions.

    – Scott Hodgin
    3 hours ago



















  • Thanks a lot scott. Solution you provided works correctly. However, I wanted to know, If i can use UNION within CTE two fetch data from two tables rather than one table and apply on top of it ? Like below query ?

    – Roxy
    4 hours ago






  • 1





    @Roxy - I added an example of how you might UNION before applying the windowing functions.

    – Scott Hodgin
    3 hours ago

















Thanks a lot scott. Solution you provided works correctly. However, I wanted to know, If i can use UNION within CTE two fetch data from two tables rather than one table and apply on top of it ? Like below query ?

– Roxy
4 hours ago





Thanks a lot scott. Solution you provided works correctly. However, I wanted to know, If i can use UNION within CTE two fetch data from two tables rather than one table and apply on top of it ? Like below query ?

– Roxy
4 hours ago




1




1





@Roxy - I added an example of how you might UNION before applying the windowing functions.

– Scott Hodgin
3 hours ago





@Roxy - I added an example of how you might UNION before applying the windowing functions.

– Scott Hodgin
3 hours ago










Roxy is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















Roxy is a new contributor. Be nice, and check out our Code of Conduct.













Roxy is a new contributor. Be nice, and check out our Code of Conduct.












Roxy is a new contributor. Be nice, and check out our Code of Conduct.
















Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230160%2fhow-to-collapse-into-a-single-row-from-multiple-rows%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Anexo:Material bélico de la Fuerza Aérea de Chile Índice Aeronaves Defensa...

Always On Availability groups resolving state after failover - Remote harden of transaction...

update json value to null Announcing the arrival of Valued Associate #679: Cesar Manara ...