CODEWITHSUNDEEP
sqlsql-server
Rashed Hasan
Rashed Hasan

Reputation: 3751

SQL complex query- ORDER BY and GROUP BY in the same query did not work properly

I have a table like that, enter image description here

Now, I want to find out the winner, who gave the correct answer most in early. I tried something like that, 

SELECT COUNT(is_correct), msisdn 
FROM [robi_contest].[dbo].[tbl_quiz_reply] 
WHERE is_correct = 1 group by msisdn

And output is- enter image description here

Now, When I try to ORDER BY creation_time , it did not give expected out but error. What should be the query to find out the winner.

Upvotes: 1

Views: 273

Answers (4)

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

SELECT COUNT(is_correct), msisdn 
FROM [robi_contest].[dbo].[tbl_quiz_reply] 
WHERE is_correct = 1 
group by msisdn 
order by max(msisdn) or Min(msisdn)

Try ordering by Max or min Which ever you want

Upvotes: 1

TriV
TriV

Reputation: 5148

I think this is what you need

SELECT TOP (1) WITH TIES
       msisdn , COUNT(is_correct) AS NumberOfCorrectAnswers,
       max(creation_time) AS LastCorrectAnswerDatetime
FROM [robi_contest].[dbo].[tbl_quiz_reply] 
WHERE is_correct = 1 
GROUP BY msisdn
ORDER BY COUNT(is_correct) DESC, max(creation_time) ASC

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Try this:

SELECT COUNT(is_correct), msisdn , min(creation_time) as min_creation
FROM [robi_contest].[dbo].[tbl_quiz_reply] 
WHERE is_correct = 1 group by msisdn
order by min_creation

The problem is that you were not returning the column as part of the group. Applying a min() to the column allows you to order by the first occurence by isdn.

Upvotes: 2

Sadikhasan
Sadikhasan

Reputation: 18600

SELECT COUNT(is_correct), msisdn 
FROM (SELECT * FROM tbl_quiz_reply WHERE is_correct = 1 ORDER BY creation_time DESC) as test
group by msisdn

Upvotes: 0

Related Questions