DISTINCT and LIMIT

Question

In tutorial SQL --> DISTINCT and LIMIT its confusing ; you start counting from 0 and look for the third record ,then the result is the forth record being first _can there be a mistake and 2nd record was to be first?

Answer 1

Starting from 0 index:

0 = record 1
1 = record 2
2 = record 3
3 = record 4

Answer 2

For being able to do that, mysql must know what to do with the other columns. You GROUP BY the column that should be unique and use a function that will tell it what to do with the others (also-called aggregate function). MAX() and COUNT() are common examples:

SELECT studentId, COUNT(courseId) AS AmountEnrolledCourses
FROM student_enrollment
GROUP BY studentId

SELECT athlete, MAX(distance) AS PersonalRecord
FROM longjump
GROUP BY athlete

Answer 3

You need to use a subquery:

select a.no, count(*) total
from (
select no from test order by no desc limit 5
) a
group by a.no
order by total desc;

Answer 4

Distinct is used to fetch unique records from table, whereas limit is used to fetch set of records starting from index zero.