Row Over Partition

Question

Row Over Partition

jeffshelix

SSCrazy

Points: 2257
More actions
January 9, 2018 at 3:58 pm

#341640
Row Over partition escapes me.
I wrote this:
1. Select
2. [COMPOSITE_PART]
3. ,[CREATED_DATE]
4. ,HTS_NUMBER
5. From
6. (Select
7. ROW_NUMBER() over (Partition by .Created_Date
8. Order by Composite_Part, Created_Date) AS rownum, HTS_NUMBER
9. from
10. [TSI].[ZATS_BROKER_FEED]
11. where
12. rownum = 1
13. AND COMPOSITE_PART = '763093C1'
based on sample code from MS training guide.
I get error near '763093C1'. I feel like i should be closing this code somehow.
thanks
January 10, 2018 at 2:43 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

jeffshelix SSCrazy Points: 2257 More actions · Answer 1

(apologies as this is continuation of my topic from yesterday)
we ended up doing that report differently. This is just educational for me so i can learn how AND WHY this works.

thanks

pietlinden SSC Guru Points: 63503 More actions · Answer 2

why what works? The ROW_NUMBER() OVER (PARTITION...) part?
PARTITION OVER is super simple - it's analogous to grouping in a totals/aggregate query, but you still get the individual records, which you don't get with a totals/aggregate query.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You start a subquery on line 6, but you never end it with a bracket nor alias the subquery. That should go between rows 10 and 11.
You'll also need to add the other columns you want returned to the sub query.

Also Partiton By .Created_Date
Either you've left out the table alias, or added an extra .
Columns don't start with . in T-SQL

p.s. Please don't post multiple questions for the same problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass