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

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

• Tuesday, June 01, 2010 5:19 AM

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!

• Moved by Friday, June 04, 2010 9:41 AM (From:Transact-SQL)
•

All Replies

• Tuesday, June 01, 2010 5:42 AM

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
• Edited by Tuesday, June 01, 2010 5:49 AM correction
•
• Tuesday, June 01, 2010 5:45 AM

what happens in the case that the sum of score2 and score3 is also equal?
• Tuesday, June 01, 2010 6:02 AM

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```
• Tuesday, June 01, 2010 6:18 AM

Both records will show in the result. What's filter criteria then?
Bipin. P | ERNST & YOUNG SSC
• Tuesday, June 01, 2010 6:54 AM

```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
```
• Tuesday, June 01, 2010 7:34 AM

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

• Tuesday, June 01, 2010 8:30 AM

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

• Friday, June 04, 2010 2:53 AM

What would the code be when querying the Access database?