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
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:
Data sample for the Target
table:
sql-server t-sql sql-server-2014 query
New contributor
add a comment |
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:
Data sample for the Target
table:
sql-server t-sql sql-server-2014 query
New contributor
What version of SQL Server are you using? 2012, 2014, 2016, etc?
– Scott Hodgin
5 hours ago
add a comment |
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:
Data sample for the Target
table:
sql-server t-sql sql-server-2014 query
New contributor
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:
Data sample for the Target
table:
sql-server t-sql sql-server-2014 query
sql-server t-sql sql-server-2014 query
New contributor
New contributor
edited 3 hours ago
MDCCL
6,75731745
6,75731745
New contributor
asked 5 hours ago
RoxyRoxy
1
1
New contributor
New contributor
What version of SQL Server are you using? 2012, 2014, 2016, etc?
– Scott Hodgin
5 hours ago
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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
)
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 mightUNION
before applying the windowing functions.
– Scott Hodgin
3 hours ago
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
)
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 mightUNION
before applying the windowing functions.
– Scott Hodgin
3 hours ago
add a comment |
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
)
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 mightUNION
before applying the windowing functions.
– Scott Hodgin
3 hours ago
add a comment |
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
)
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
)
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 mightUNION
before applying the windowing functions.
– Scott Hodgin
3 hours ago
add a comment |
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 mightUNION
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
add a comment |
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.
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
What version of SQL Server are you using? 2012, 2014, 2016, etc?
– Scott Hodgin
5 hours ago