SQL help with partition

Question

SQL help with partition

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

don075 SSC-Addicted Points: 425 More actions · Answer 1

Thanks for the response. Much appreciated. It worked for that sample data because the dates were the same.

When I have different dates, it resets the counter.

My apologies, if I am changing my requirement. I just want to flag the very first record sorted by OrdDate where PrgFld is not 'XXX'

I do this by creating another table where PrgFld is not 'XXX' and adding the partition to create the counter.

Then I delete those records from the main table and add the records from the newly created table. I think that is too much work on the server when we have 100,000+ records and not the best use of sql.

Just want to know whether it could be done in the select without creating additional tables. New sample data.

insert into #t1 values('4444','A400','2023-06-22','XXX','20221021')
insert into #t1 values('4444','A400','2023-06-22','XXX','20221021')
insert into #t1 values('4444','A400','2023-05-23','AMB1','20220915')
insert into #t1 values('4444','A400','2023-05-24','AMB1','20220915')
insert into #t1 values('4444','A400','2023-06-25','AMB1','20221021')
insert into #t1 values('4444','A400','2023-06-26','CCC','20221021')
insert into #t1 values('4444','A400','2023-06-27','AMB1','20220915')
insert into #t1 values('4444','A400','2023-06-28','AMB1','20220915')

Dennis Jensen SSChasing Mays Points: 606 More actions · Answer 2

It was not clear to me what you are requesting but I am going to state that 100k records is infitesimal as we deal with millions of records on a daily basis and billions or records is not uncommon.

Next you are assuming way too much -- in that you have not presented the previous query you are basing this query on which means if someone happens to recall that previous query they might make the connection and for those that do not -- well you get left out in the cold as they are not going to even try to help you.

It is best that you explain your question in extreme detail as that will be the best case of you getting a quality answer. Which is to say, this question is extremely lacking in content and needs an extreme upgrade.

Ultimitely you help yourself by helping us help you.

Steve Collins SSCrazy Points: 2174 More actions · Answer 3

Maybe add a boolean column to the partition

select *, v.grp,
       [test33] = case when v.grp=1 
                       then row_number() over(partition by VID, Acc, v.grp order by OrdDate) 
                       else 0 end
from #t1
     cross apply (values (case when PrgFld='XXX' then 0 else 1 end)) v(grp) 
order by VID, Acc, OrdDate;

This reply was modified 1 year, 7 months ago by Steve Collins. Reason: Removed IN from WHERE clause

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2174 More actions · Answer 4

Doh, well I'd like to edit my edit in the previous post. Instead of "Removed IN from WHERE clause" it should say "Removed IN from WHEN condition" or something like that

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

don075 SSC-Addicted Points: 425 More actions · Answer 5

So far this solution gives me the result I am looking for. Much appreciated. Will be testing this with live data. Again apologies as the requirement was changed in the middle of the forum.