CODEWITHSUNDEEP
sqlsql-server
timton
timton

Reputation: 61

How to remove duplicates in one column and select the first top one only

select * from 
(select M.* from zstatistics M JOIN ZEntrycriteria C ON  M.coursecode=C.Course_code 
    where C.Maths <= @maths 
    AND C.Science <= @science 
    AND C.English <= @english 
    And C.Ict <= @ict 
    And C.History <= @history 
    And C.Geography <= @geography 
    And C.Art <= @Art 
UNION 
    select M.* from zsuggestions M JOIN ZEntrycriteria C ON M.coursecode=C.Course_code 
    where C.Maths <= @maths 
    AND C.Science <= @science 
    AND C.English <= @english 
    And C.Ict <= @ict 
    And C.History <= @history 
    And C.Geography <= @geography 
    And C.Art <= @Art

) t 

ORDER BY sqrt( power(t.Maths - @maths, 2) + power(t.Science - @science,2) + power(t.English - @english,2) + power(t.Ict - @ict,2) + power(t.History-@history,2) + power(t.Geography - @geography,2) + power(t.Art - @Art,2))

The above sql query give the following answer.

SchoolName| CourseName| Maths| Science| English| History|Geography|Art|ICT

xyz       |  abcd     |45    |85      |85      |  95    |58       |65 |85

xyz       |  abcd     |85    |95      | 68     |80      |  100    |40 |80

kkk       |  ku       |60    |50      | 54     | 82     |82       |58 |95

.
.
.

As you can see now CourseName column has two "abcd" with different data. My question is how do i get only the top row with "abcd". I tried with "group by CourseName" and "Partition by" but doesnt work. Any help would be appreciated.

I think I found the answer here! but I do not understand how to use it on my scenario (Sorry for bad English )

Upvotes: 1

Views: 228

Answers (1)

Vecchiasignora
Vecchiasignora

Reputation: 1315

try this

select * from (select ROW_NUMBER() over (partition by CourseName order by  sqrt( power(t.Maths - @maths, 2) + power(t.Science - @science,2) + power(t.English - @english,2) + power(t.Ict - @ict,2) + power(t.History-@history,2) + power(t.Geography - @geography,2) + power(t.Art - @Art,2)) ) rownumb , t.* from 
(select M.* from zstatistics M JOIN ZEntrycriteria C ON  M.coursecode=C.Course_code 
    where C.Maths <= @maths 
    AND C.Science <= @science 
    AND C.English <= @english 
    And C.Ict <= @ict 
    And C.History <= @history 
    And C.Geography <= @geography 
    And C.Art <= @Art 
UNION 
    select M.* from zsuggestions M JOIN ZEntrycriteria C ON M.coursecode=C.Course_code 
    where C.Maths <= @maths 
    AND C.Science <= @science 
    AND C.English <= @english 
    And C.Ict <= @ict 
    And C.History <= @history 
    And C.Geography <= @geography 
    And C.Art <= @Art 
) t) a
where  a.rownumb=1

Upvotes: 2

Related Questions