user3447653 user3447653 - 19 days ago 5
SQL Question

Query to get the first and last row in a table

I ran the following query to get the first and last row from a table.

SELECT Start, End, Start1, End1
FROM
(SELECT Start AS Start, End AS End
FROM [TB1] WHERE Id = 251 ORDER BY DateTime DESC LIMIT 1),
(SELECT Start1 AS Start1, End1 AS End1
FROM [TB2] WHERE Id = 251 ORDER BY DateTime LIMIT 1);


I get the output as follows:

Start End Start1 End1
25.32 -98.55 null null
null null 29.81 -98.32


I am trying to get the output in the below format:

Start End Start1 End1
25.32 -98.55 29.81 -98.32

Answer

I feel like your question is "partial" and next will be how to do the same for all Id! See below

SELECT Id, Start, End, Start1, End1
FROM
(SELECT Id, Start, End 
 FROM (
   SELECT
     Id, Start AS Start, End AS END, 
     ROW_NUMBER() OVER(PARTITION BY Id ORDER BY DateTime DESC) AS win
   FROM [TB1])
 WHERE win = 1
) AS t1
JOIN
(SELECT Id, Start1, End1 
 FROM (
   SELECT
     Id, Start1 AS Start1, End1 AS End1,
     ROW_NUMBER() OVER(PARTITION BY Id ORDER BY DateTime) AS win
   FROM [TB2])
 WHERE win = 1
) AS t2
ON t1.Id = t2.Id
Comments