Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
Data Set:
col1,col2,col3,col4.col5
(A,b,c,d,e)
(A,z,p,q,r)
(F,s,t,y,v)
(F,w,x,y,z)
(F,p,x,r,t)
(G,m,n,o,p)
(G,m,i,q,s)

Actually I need result set that has all the columns and only single row for each capital letter values among the all same capital letter values.

Result set should be like:
(A,b,c,d,e)
(F,p,q,r,t)
(G,k,i,q,s)

or
(A,z,p,q,r)
(F,w,x,y,z)
(G,m,n,o,p)

any of three rows.

Actually I have detail records of so many companies in which some where company name are redundant and I have to select single records for each company on the basis of their name. Here I use Capital letters for company name.

I hope you realize the question well.

Please provide a select statement I will be thankful to you all.

share|improve this question
1  
Have you tried anything? –  JNK Jul 25 '12 at 12:45
    
yeha I tried my basics sql like group by and joins... but not able to find the perfect solution . Thaks to SHARK. He provide the right soulution. –  JP Chauhan Jul 25 '12 at 13:36
add comment

1 Answer

up vote 4 down vote accepted
;with LetterSet as
(
    select *,
        row_number() over(partition by FirstCol order by SecondCol) as rownum
    from YourLettersTable
)
select
    FirstCol,
    SecondCol,
    ThirdCol,
    FourthCol,
    FifthCol
from LetterSet
where rownum = 1

Likewise, you could have rownum = 2 for your second result set.

share|improve this answer
    
> over(partition by FirstCol order by FirstCol) Shark, why not over(partition by FirstCol order by Col2, col3,...) ? I think you'll can get different results between runs. –  msi77 Jul 25 '12 at 15:59
    
Ah good point. Error on my part. Fixed, thanks! –  Thomas Stringer Jul 25 '12 at 19:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.