locked
need a query RRS feed

  • Question

  • hello

    i have a table ,that has a structure and data

    ID                 year         month       days            kind

    125              1985         01               30                 1

    125              1999         05               25                 1

    125              1999         05               30                 2

    125              1999         05                17                3

    126              2001        12                20                 2

    126              2010        01                30                 1

    126              2010        01                18                 2 

    127              2007        08                14                 1

    i need a query that:

    1- all ID should be displayed

    2- For duplicate rows (in The same year and month) should be displayed only with the highest value

    OUTPUT query:

    ID                 year         month       days            kind

    125              1985         01               30                 1

    125              1999         05               30                 2

    126              2001        12                20                 2

    126              2010        01                30                 1

    127              2007        08                14                  1

    thanks;

    Saturday, May 31, 2014 8:11 AM

Answers

  • Please try this:

    with c1 as (
    select ID, year, month, days, kind 
    		, ROW_NUMBER() over ( partition by year, month order by days desc) as rn
    from test
    ) 
    select ID, year, month, days, kind
    from c1
    where rn = 1
    order by id


    sqldevelop.wordpress.com

    • Marked as answer by Ashkan209 Sunday, June 1, 2014 8:23 AM
    Saturday, May 31, 2014 9:09 AM
  • Is it what you're looking for:

    with c1 as (
    select ID, year, month, days, kind 
    		, ROW_NUMBER() over ( partition by year, month order by days desc) as rn
    from test
    ) 
    select ID, 
    count(*) over (partition by ID) as cnt,
    year, month, days, kind
    from c1
    where rn = 1
    order by id


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Saeid Hasani Sunday, June 1, 2014 5:48 PM
    • Marked as answer by Ashkan209 Saturday, June 7, 2014 5:38 AM
    Sunday, June 1, 2014 5:09 PM

All replies

  • Please try this:

    with c1 as (
    select ID, year, month, days, kind 
    		, ROW_NUMBER() over ( partition by year, month order by days desc) as rn
    from test
    ) 
    select ID, year, month, days, kind
    from c1
    where rn = 1
    order by id


    sqldevelop.wordpress.com

    • Marked as answer by Ashkan209 Sunday, June 1, 2014 8:23 AM
    Saturday, May 31, 2014 9:09 AM
  • thanks for answer

    i need other query for table that Counting rows with condition:

    non duplicate(for similar year & month)+ rows Only one of the duplicate rows to be counted (for similar year & monthjust one rows is counted)

    *************************

    Outputquery:

    ID           Count(id)

    125           2                      >>  1 row (non duplicate) + 1 of 3 rows have similar year & month =2

    126           2

    127           1

    Sunday, June 1, 2014 9:26 AM
  • Is it what you're looking for:

    with c1 as (
    select ID, year, month, days, kind 
    		, ROW_NUMBER() over ( partition by year, month order by days desc) as rn
    from test
    ) 
    select ID, 
    count(*) over (partition by ID) as cnt,
    year, month, days, kind
    from c1
    where rn = 1
    order by id


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Saeid Hasani Sunday, June 1, 2014 5:48 PM
    • Marked as answer by Ashkan209 Saturday, June 7, 2014 5:38 AM
    Sunday, June 1, 2014 5:09 PM