Multiple tables associated query problems for help-CodePudding

Hello, everyone:
I just started to contact database, a few days before the interview encountered such a problem: a table FIRST, table structure and data below:

(when no ID in the exam, this column is I don't know how many rows of data and how to locate the own plus)
Title requirement is: will record according to the value of the type classification, will each type of the value of the CODE of the ASC Ⅱ in the second-largest record query, tip is a table and const directive,
I thought for a long time, use the

 select const (*), type the from the first group by type.

gets the number of each type, through the expression can also locate the second-largest record which line should be,
My question is:
1, how to query a list specific to which the record
2, what kind of instruction can I check out of the results as a temporary table and original table,
3, the original value is the number of CODE, compare the ASC Ⅱ CODE is simpler, if also contains the alphabet, this is how to implement in the SQL statement,
Hope to get help, thank you,

CodePudding user response:

Select * from
(select a. *, row_num () over (partition by type order by ASCII (code) as an rn from t1 a) b where b.r n2

CodePudding user response:

The SELECT Max (a.c ode), a.t ype FROM first AS a Left Join first AS b ON a.t ype=b.t ype AND a.c ode CodePudding user response:

 
# of each record has a record of all of the same type, statistical code is greater than the number of records in this section, if there is only a record of code is greater than the record, this article is the record of this article code 
# is the second largest and exists to record this article screening out, 

Select * 
From the first as a 
Where the exists (select count (*) as num 
From the first 
Where a.t ype=type and a.c ode & lt; Code 
Having num=1);

CodePudding user response:

Query code acsii maximum, and then find out all smaller than the maximum value after all of the code values are grouped to the maximum value of the group is the second largest
Select Max (code) as code, type the from first
The where (ASCII code) & lt;
(select Max (ASCII (code)) from bb)
Group by type