SQLTeam.com | Weblogs | Forums

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

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

Is it this?

select product,Item from table
order by product

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

like this,
lst%202

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

Thanks, that looks very good and it works.

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

hi Johnse

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

sorry