Find last and first row for every id-CodePudding

I have this table:

id	RANK
111	1
111	2
111	3
222	1
222	2

I want to add two colums that will show if this is the first/last row for each id

id	first	last
111	YES	NO
111	NO	NO
111	NO	YES
222	YES	NO
222	NO	YES

CodePudding user response：

Let's first point out that sorting without column to sort this is no good idea.

Usually, an id is unique and will be incremented, so it will already be sufficient to order by id.

If this is not the case, there should be at least be another column with a meaningful value (for example also an incrementing number or a datetime) which can be used to sort the result.

So you should fix your table design if possible and add such a column or make your already existing id column unique.

If this is not possible and you really have to order just by the row number, you could do following:

SELECT id,
CASE WHEN rn = 1 THEN 'YES' ELSE 'NO' END AS first,
CASE WHEN rn = COUNT(*) OVER (PARTITION BY id) 
THEN 'YES' ELSE 'NO' END AS last 
FROM 
(
  SELECT
    id,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) rn
  FROM yourtable
);

If you have a column to sort (let's name it "rank"), this will be much safer:

SELECT id,
CASE WHEN rn1 = 1 THEN 'YES' ELSE 'NO' END AS first,
CASE WHEN rn2 = 1 THEN 'YES' ELSE 'NO' END AS last
FROM 
(
  SELECT
   id,
   ROW_NUMBER() OVER (PARTITION BY id ORDER BY rank) rn1,
   ROW_NUMBER() OVER (PARTITION BY id ORDER BY rank DESC) rn2
  FROM yourtable
);

CodePudding user response：

Here's one option:

Sample data:

SQL> with
  2  test (id, rank) as
  3    (select 111, 1 from dual union all
  4     select 111, 2 from dual union all
  5     select 111, 3 from dual union all
  6     select 222, 1 from dual union all
  7     select 222, 2 from dual
  8    ),

Query begins here:

  9  temp as
 10    (select id,
 11       rank,
 12       first_value(rank) over (partition by id) rnk_min,
 13       last_value(rank)  over (partition by id ) rnk_max
 14     from test
 15    )
 16  select id,
 17    case when rank = rnk_min then 'Yes' else 'No' end first,
 18    case when rank = rnk_max then 'Yes' else 'No' end last
 19  from temp
 20  order by id, rank;

        ID FIRST   LAST
---------- ------- -------
       111 Yes     No
       111 No      No
       111 No      Yes
       222 Yes     No
       222 No      Yes

SQL>

CodePudding user response：

If you don't have rows with the same rank per id, you may use lag/lead functions to mark first and last rows with a flag using default argument of these functions, which is used when the function leaves a window boundary.

with sample_tab (id, rank) as (
  select 111, 1 from dual union all
  select 111, 2 from dual union all
  select 111, 3 from dual union all
  select 222, 1 from dual union all
  select 222, 2 from dual
)
select
  id
  , lag('No', 1, 'Yes') over(partition by id order by rank asc) as last
  , lead('No', 1, 'Yes') over(partition by id order by rank asc) as last
from sample_tab

ID	LAST	LAST
111	Yes	No
111	No	No
111	No	Yes
222	Yes	No
222	No	Yes

If the data may have the same rank for multiple rows per id, you may use the same technique (a case when function goes beyound window boundary) with coalesce.

with sample_tab (id, rank) as (
  select 111, 1 from dual union all
  select 111, 2 from dual union all
  select 111, 2 from dual union all
  select 222, 1 from dual union all
  select 222, 2 from dual
)
select
  id
  , coalesce(max('No') over(
      partition by id order by rank asc
      /*RANGE for logical offset,
      setting the same flag for a group of first/last rows*/
      range between 1 preceding and 1 preceding
  ), 'Yes') as first
  , coalesce(max('No') over(
      partition by id order by rank asc
      range between 1 following and 1 following
  ), 'Yes') as last
from sample_tab

ID	FIRST	LAST
111	Yes	No
111	No	Yes
111	No	Yes
222	Yes	No
222	No	Yes

fiddle