Sfrgttk

I hate taking lectures, can I still survive in academia?

Prove that a cyclic group with only one generator can have at most 2 elements

How should I ship cards?

Hollowed circle with crossed line and arrow

Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?

Which was the first story to feature space elevators?

Does human life have innate value over that of other animals?

Uncountable set with a non-discrete metric

Can a planet be tidally unlocked?

Why are energy weapons seen as more acceptable in children's shows than guns that fire bullets?

Can a rabbi conduct a marriage if the bride is already pregnant from the groom?

Pictures from Mars

Do error bars on probabilities have any meaning?

How to make clear what a part-humanoid character looks like when they're quite common in their world?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Why is Shelob considered evil?

Movie where a woman is running away from a plant creature which rips her shorts?

What prevents people from lying about where they live in order to reduce state income taxes?

Why does the current not skip resistors R3 and R5 when R6 and R4 have no resistance?

Can I legally make a website about boycotting a certain company?

Apparently I’m calling random numbers but there's nothing in the call log

Negotiating 1-year delay to my Assistant Professor Offer

"Cheaper by the dozen" phrase origin?

Subcaptions in subfigures

mysql Quiz leaderboard filter by points, time taken

Best way to query huge dataSQL join query to show rows with non-existent rows in one tableDatabase/Storage engine suggestion for a project involving heavy inserts?Is Data Warehousing possible in MySQL and PostgresSQLSelecting, ranking and applying a point score based on rankProper usage of Group by in Inner JoinMySQL - Finding Gaps of Date Ranges - Overlapping DatesMySQL partition on 2 tables one with millions of rows the other with 100's of millionsHow to improve query count execution with mySql replicate?SUM n rows of each group with ORDER BY and LIMIT where the LIMIT is based off of another table

0

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken.

here is a link to a sql fiddle http://sqlfiddle.com/#!2/65fbf0/1

I am really struggling as i need to filter the results by two columns for one user, my ideal result would be

Results for Quiz id 1

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,15 2015|  1        |

| 3        |  2       | 0.5          | May,15 2015|  1        |

Then a separate query for all quiz's showing the results from the last week.

Results from all Quizzs

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,13 2015|  3        |

| 3        |  2       | 0.5          | May,12 2015|  2        |

mysql performance group-by greatest-n-per-group

share|improve this question

asked May 26 '15 at 23:09

GismmoGismmo

1

bumped to the homepage by Community♦ 14 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean by filtering? Ordering maybe? And please produce a minimal example without all the irrelevant columns, then include the table definition, sample data and your best try at the query here.
  
  – dezso
  May 27 '15 at 4:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have a simply mysql query in for now, but i need to limit this to a user_id best entry: SELECT user_id, quiz_id, points, time_spend, start_dt FROM ProQzQuizReport WHERE quiz_id = 1 ORDER by points DESC, time_spend ASC I have updated the sql fiddle sqlfiddle.com/#!2/65fbf0/7
  
  – Gismmo
  May 27 '15 at 8:24
  
  
  

  
  
  
  

add a comment | 

0

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken.

here is a link to a sql fiddle http://sqlfiddle.com/#!2/65fbf0/1

I am really struggling as i need to filter the results by two columns for one user, my ideal result would be

Results for Quiz id 1

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,15 2015|  1        |

| 3        |  2       | 0.5          | May,15 2015|  1        |

Then a separate query for all quiz's showing the results from the last week.

Results from all Quizzs

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,13 2015|  3        |

| 3        |  2       | 0.5          | May,12 2015|  2        |

mysql performance group-by greatest-n-per-group

share|improve this question

asked May 26 '15 at 23:09

GismmoGismmo

1

bumped to the homepage by Community♦ 14 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean by filtering? Ordering maybe? And please produce a minimal example without all the irrelevant columns, then include the table definition, sample data and your best try at the query here.
  
  – dezso
  May 27 '15 at 4:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have a simply mysql query in for now, but i need to limit this to a user_id best entry: SELECT user_id, quiz_id, points, time_spend, start_dt FROM ProQzQuizReport WHERE quiz_id = 1 ORDER by points DESC, time_spend ASC I have updated the sql fiddle sqlfiddle.com/#!2/65fbf0/7
  
  – Gismmo
  May 27 '15 at 8:24
  
  
  

  
  
  
  

add a comment | 

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken.

here is a link to a sql fiddle http://sqlfiddle.com/#!2/65fbf0/1

I am really struggling as i need to filter the results by two columns for one user, my ideal result would be

Results for Quiz id 1

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,15 2015|  1        |

| 3        |  2       | 0.5          | May,15 2015|  1        |

Then a separate query for all quiz's showing the results from the last week.

Results from all Quizzs

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,13 2015|  3        |

| 3        |  2       | 0.5          | May,12 2015|  2        |

mysql performance group-by greatest-n-per-group

share|improve this question

asked May 26 '15 at 23:09

GismmoGismmo

1

Results for Quiz id 1

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,15 2015|  1        |

| 3        |  2       | 0.5          | May,15 2015|  1        |

Results from all Quizzs

---------------------------------------------------------------   

| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |

| 1        |  3       | 0.5          | May,15 2015|  1        |

| 2        |  3       | 0.8          | May,13 2015|  3        |

| 3        |  2       | 0.5          | May,12 2015|  2        |

share|improve this question

asked May 26 '15 at 23:09

GismmoGismmo

1

share|improve this question

asked May 26 '15 at 23:09

GismmoGismmo

1

asked May 26 '15 at 23:09

GismmoGismmo

1

asked May 26 '15 at 23:09

GismmoGismmo

1

1 Answer
1

active

oldest

votes

0

Solution to the query:

select *

from (

  select *

  from ProQzQuizReport

  order by points desc, time_spend asc) a

group by user_id;

http://sqlfiddle.com/#!2/65fbf0/9

share|improve this answer

answered May 27 '15 at 9:21

GismmoGismmo

1

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f102515%2fmysql-quiz-leaderboard-filter-by-points-time-taken%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Solution to the query:

select *

from (

  select *

  from ProQzQuizReport

  order by points desc, time_spend asc) a

group by user_id;

http://sqlfiddle.com/#!2/65fbf0/9

share|improve this answer

answered May 27 '15 at 9:21

GismmoGismmo

1

add a comment | 

0

Solution to the query:

select *

from (

  select *

  from ProQzQuizReport

  order by points desc, time_spend asc) a

group by user_id;

http://sqlfiddle.com/#!2/65fbf0/9

share|improve this answer

answered May 27 '15 at 9:21

GismmoGismmo

1

add a comment | 

Solution to the query:

select *

from (

  select *

  from ProQzQuizReport

  order by points desc, time_spend asc) a

group by user_id;

http://sqlfiddle.com/#!2/65fbf0/9

share|improve this answer

answered May 27 '15 at 9:21

GismmoGismmo

1

select *

from (

  select *

  from ProQzQuizReport

  order by points desc, time_spend asc) a

group by user_id;

share|improve this answer

answered May 27 '15 at 9:21

GismmoGismmo

1

answered May 27 '15 at 9:21

GismmoGismmo

1

answered May 27 '15 at 9:21

GismmoGismmo

1