need a query

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;

Answer 1

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

Answer 2

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

Answer 3

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