How to select top value in a column with condition based on other column?

Question

Hi Need some help with selecting the top score in a score keeping program

I need to select the person with highest score in scoreCatagory1 , in the case that the score is the same, it determines the top score by comparing the sum of scoreCatagory2 and scoreCatagory3

sample data

name/scoreCatagory1/scoreCatagory2/scoreCatagory3

personA/90/90/90

personB/9090/80

personc/80/90/90

Thanks!

Answer 1

Hi

Try This

with _cte1 as
(
SELECT * FROM Table1 WHERE sc1 = (SELECT MAX(SC1) MaxSC1 FROM Table1)
), cte2 as
(
SELECT TOP 1 name, SUM(SC2 + SC3) SumSC2SC3 FROM _cte1
GROUP by name
order by SumSC2SC3 desc
)
SELECT a.* FROM Table1 A
inner join cte2 B on a.name = b.name

---

Bipin. P | ERNST & YOUNG SSC

Answer 2

what happens in the case that the sum of score2 and score3 is also equal?

Answer 3

what happens in the case that the sum of score2 and score3 is also equal?

with t(name,s1,s2,s3) as(
	select 'A',90,90,90
	union
	select 'B',90,90,80
	union
	select 'C',80,90,90
)
select top 1 name,s1,s2,s3 from (
 select name,s1,s2,s3,
 row_number()over(partition by s1 order by s2+s3 desc) rn from t
) tt where rn=1 order by s1 desc 


name	s1	s2	s3
A	90	90	90

Answer 4

Both records will show in the result. What's filter criteria then?

---

Bipin. P | ERNST & YOUNG SSC

Answer 5

with t(name,s1,s2,s3) as(
	select 'A',90,90,90
	union
	select 'B2',90,90,70
	union	
	select 'C',80,90,90
	union
	select 'G',80,90,90	
	union
	select 'D',90,90,90	
	union
	select 'E',90,70,110	
	union
	select 'F',90,110,70
)
select t.name,t.s1,t.s2,t.s3 from t 
inner join (
	select top 1 s1,s2+s3 total from (
	 select name,s1,s2,s3,
	 row_number()over(partition by s1 order by s2+s3 desc) rn from t
	) tt where rn=1 order by s1 desc 
) b on t.s1=b.s1 and t.s2+t.s3=b.total


name	s1	s2	s3
A	90	90	90
D	90	90	90
E	90	70	110
F	90	110	70

Answer 6

Can you explain how the components, such as row_number() , over, partition by, of the query works?

Also I'm testing it with an access database, it says Syntax error(missing operator) in query expression'C_CAT2+C_CAT3 TOTAL.

SELECT CID, C_NAME,C_CAT2,C_CAT3,C_CAT4 FROM CONTESTANTS
INNER JOIN
(
  SELECT TOP 1 C_CAT4, C_CAT2 + C_CAT3 TOTAL FROM
 (
   SELECT CID,C_NAME C_CAT2, C_CAT3, C_CAT4, ROW_NUMBER() OVER (PARTITION by C_CAT2 ORDER BY C_CAT2+C_CAT3 DESC) RN FROM CONTESTANTS
  ) TT WHERE RN=1 ORDER BY C_CAT1 DESC
)B on CONTESTANTS.C_CAT4 = B.C_CAT4 AND CONTESTANTS.C_CAT2+CONTESTANTS.C_CAT3 = B.TOTAL

Answer 7

Can you explain how the components, such as row_number() , over, partition by, of the query works?

Also I'm testing it with an access database, it says Syntax error(missing operator) in query expression'C_CAT2+C_CAT3 TOTAL.

SELECT CID, C_NAME,C_CAT2,C_CAT3,C_CAT4 FROM CONTESTANTS
INNER JOIN
(
  SELECT TOP 1 C_CAT4, C_CAT2 + C_CAT3 TOTAL FROM
 (
   SELECT CID,C_NAME C_CAT2, C_CAT3, C_CAT4, ROW_NUMBER() OVER (PARTITION by C_CAT2 ORDER BY C_CAT2+C_CAT3 DESC) RN FROM CONTESTANTS
  ) TT WHERE RN=1 ORDER BY C_CAT1 DESC
)B on CONTESTANTS.C_CAT4 = B.C_CAT4 AND CONTESTANTS.C_CAT2+CONTESTANTS.C_CAT3 = B.TOTAL

a lot of difference between SQL Server and Access, take a look

http://msdn.microsoft.com/en-us/library/ms186734.aspx

row_number()over() seems only for SQL Server 2005 and 2008

Answer 8

What would the code be when querying the Access database?