Saving 10 Biggest Fish (with.MySQL)

Here’s a look at saving 10 of the biggest fish (with.MySQL).

I want to solve the coding test problem, find out how to solve it differently from the retrospective of the problem I solved, and get to know.

Let’s get to the problem first.

Problem

Please write a SQL statement that outputs the ID and length of the 10 largest fish in the FISH_INFO table.

Please sort the results in descending order based on the length, and if the lengths are the same, please sort in ascending order for the fish’s ID.

However, none of the 10 largest fish are less than 10 cm long.

ID column name should be ID and length column name should be LENGTH.

Problem Description

The FISH_INFO table used by the fishing app contains information on fish caught.

The structure of the FISH_INFO table is as follows, and ID, FISH_TYPE, LENGTH, and TIME indicate the ID of the fish caught, the type of fish (number), the length of the fish caught (cm), and the date of the fish caught.

FISH_INFO Table

However, if the caught fish is less than 10 cm long, the LENGTH is NULL, and there is no NULL in the LENGTH.

problem solving

SELECT ID, LENGTH
FROM FISH_INFO
ORDER BY LENGTH DESC, ID
LIMIT 10;

Solution Description

This SQL query queries and returns the top 10 data based on fish length information.

The query extracts data from the FISH_INFO table, and the main components are.

First, the SELECT section specifies the columns to look up.

ID is the unique identifier of the fish, LENGTH is the length of the fish, and these two columns are output as a result.

The FROM section then specifies the default table on which to run the query.

In this case, the FISH_INFO table is used.

Next, sort the results through the ORDER BY clause.

There are two sorting criteria, the first is LENGTH DESC.

These are arranged in descending order based on the length of the fish, starting with the largest fish.

The second sorting criterion is ID, and if the lengths are the same, sort them in ascending order based on ID.

This allows fish of the same length to be aligned in order of unique ID.

Finally, LIMIT 10 selects and returns only the top 10 rows from the query results.

It serves to return information about the 10 largest fish among the sorted results.

This query allows you to look up the IDs and lengths of the 10 largest fish.

This makes it easy to analyze fish of a specific size or larger or to identify the top fish by size.