Select the 1st, 2nd, 3rd, etc items

1

Hello,

Can someone advise or show me a working example of how you select the 1st, 2nd, 3rd, etc items from a product, then the second, third or fourth item from product B, etc.

example, the first item in A, the second item in A, the third item in A, and then B, and C etc. These items are not 1, 2, 3, etc they are only there for representation purpose but the first, second, third, record of product A, first, second, third record of product B, etc. Hope you know what I mean. How do we do that, if is even possible ?

lst

2

Is it this?

select product,Item from table
order by product

3

No because that would be as what is shown but without field list.

like this,
lst%202

4

Use rownumber and CTE:

with listItems
as (
select *, row_number() over(Partition By Product Order By list) As itemNumber
)
Select *
From listItems
Where itemNumber = 1
5

Thanks, that looks very good and it works.

6

hi

i tried to do this ...!!!!

hope it helps
i love feedback thanks
:slight_smile: :slight_smile:

drop create data 
drop table #items
go 


create table #items
(
list int,
product varchar(1),
Item int
)
go

insert into #items select 1,'A',1
insert into #items select 2,'A',2
insert into #items select 3,'A',3
insert into #items select 4,'B',1
insert into #items select 5,'B',1
insert into #items select 6,'C',2
insert into #items select 7,'C',1
insert into #items select 8,'C',2
insert into #items select 9,'C',3
go 

select * from #items
go
SQL 
SELECT a.* 
FROM   (SELECT Row_number() 
                 OVER( 
                   partition BY product 
                   ORDER BY list) AS rn, 
               * 
        FROM   #items) a 
ORDER  BY rn, 
          product

image

7

hi Johnse

who was the thanks to ??? just curious please let me know

sorry