Post reply

Over (Partition By)

  • November 1, 2011 at 1:00 pm

    #245430

    Hi Guys

    AdventureWorks 2008 DB

    When using the following Code

    select distinct SalesOrderID

    ,SalesOrderDetailID

    , SUM (OrderQty) over (partition by SalesOrderID)as "Order Quantity Total"

    , cast (AVG (UnitPrice) over (partition by SalesOrderID) as decimal (18,2)) as "Average Unit Price"

    , cast (sum (LineTotal) over (partition by SalesOrderID) as decimal (18,2)) as "Line Total"

    from Sales.SalesOrderDetail

    order by SalesOrderID

    Or This

    select SOD.SalesOrderID

    , SOD.SalesOrderDetailID

    , amount.[Order Quantity Total]

    , amount.[Average Unit Price]

    , amount.[Line Total]

    from Sales.SalesOrderDetail SOD

    inner join

    (select

    salesorderID, sum (OrderQty) as "Order Quantity Total"

    , AVG (UnitPrice)as "Average Unit Price"

    , cast(sum (LineTotal)as decimal (18,2)) as "Line Total"

    from Sales.SalesOrderDetail

    group by salesorderID) amount

    on amount.SalesOrderID = SOD.SalesOrderID

    order by SalesOrderID

    Which returns the same results

    How do I remove the duplicates in the result?

    Thanks

    Derek

  • November 1, 2011 at 1:24 pm

    #1401807

    Did you notice your first query has a "distinct" keyword ?

    Guess what that does.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 1, 2011 at 1:25 pm

    #1401808

    SalesOrderDetailId makes each row unique.

    Can you please explain what are the duplicates you want to eliminate?

  • November 1, 2011 at 1:33 pm

    #1401812

    Derek...given the following results (from AW2008)...what results are you expecting?

    SalesOrderIDSalesOrderDetailIDOrder Quantity TotalAverage Unit PriceLine Total

    43659 1 26 1193.6426 20565.62

    43659 2 26 1193.6426 20565.62

    43659 3 26 1193.6426 20565.62

    43659 4 26 1193.6426 20565.62

    43659 5 26 1193.6426 20565.62

    43659 6 26 1193.6426 20565.62

    43659 7 26 1193.6426 20565.62

    43659 8 26 1193.6426 20565.62

    43659 9 26 1193.6426 20565.62

    43659 10 26 1193.6426 20565.62

    43659 11 26 1193.6426 20565.62

    43659 12 26 1193.6426 20565.62

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • November 2, 2011 at 12:39 am

    #1401979

    ALZDBA (11/1/2011)

    Did you notice your first query has a "distinct" keyword ?

    Guess what that does.

    Noticed that - Thanks

    Even without the distinct, the result is the same

    Noticed that the SalesOrderDetailID column is making it unique

    One thing, the total rows returned is 121311 but the last value for SalesOrderDetailID is 121317

    Is that normal?

    Thanks

  • November 2, 2011 at 12:46 am

    #1401982

    derekr 43208 (11/2/2011)

    One thing, the total rows returned is 121311 but the last value for SalesOrderDetailID is 121317

    Is that normal?

    Yep. It's the number of attempted inserts, not the number of rows. Identity fields will gap, it's expected.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 2, 2011 at 1:05 am

    #1401987

    Evil Kraig F (11/2/2011)

    derekr 43208 (11/2/2011)

    One thing, the total rows returned is 121311 but the last value for SalesOrderDetailID is 121317

    Is that normal?

    Yep. It's the number of attempted inserts, not the number of rows. Identity fields will gap, it's expected.

    Thanks a lot

    Derek

  • November 2, 2011 at 3:21 am

    #1402018

    delete from [table_name] where %%lockres%% in (

    select MAX(%%lockres%%) from [table_name]

    group by [column_name])

    With the help of the above query you will meet your needs.

    where [table_name] is name of the table from which you want to remove duplicates records

    and [column_name] is name of the column from which you want to remove duplicates records.

  • November 2, 2011 at 3:25 am

    #1402020

    Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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