The field location secret when the last function is used for querying after the SQL grouping

 

When the last function is used to query the SQL group, the field location will affect the sorting rule of the query results of each group. If the last function is used to obtain the last query result, effective sorting is very important.

 

Example:

Access View Design (1 ):

Select tower information. number, monitoring common data. time, monitoring general data. line name, monitoring common data. tower number, monitoring common data. tower name (monitoring common data. SIM card ID) as sim card number, monitoring common data. number of lightning strikes in Phase A, monitoring common data. number of lightning strikes in Phase B, monitoring common data. lightning strikes in Phase C
From monitoring conventional data inner join tower information on monitoring conventional data. SIM card id = tower information. SIM card number
Union select number, #1999-01-01 12:01:01 #, line name, Tower number, tower name, SIM card number, 0 as a lightning strikes, 0 as B lightning strikes, 0 as C-phase lightning strikes
From tower Information
Order by time;

 

Access View Design (II ):

Select tower information. number, monitoring common data. line name, monitoring common data. tower number, monitoring common data. tower name (monitoring common data. SIM card ID) as sim card number, monitoring common data. number of lightning strikes in Phase A, monitoring common data. number of lightning strikes in Phase B, monitoring common data. lightning strikes in Phase C
From monitoring conventional data inner join tower information on monitoring conventional data. SIM card id = tower information. SIM card number
Union select No., line name, tower no., tower no., SIM card No., 0 as a lightning strikes, 0 as B lightning strikes, 0 as C lightning strikes
From tower Information

Order by time;

 

 

Query through this view group:

Strsql = "select number, line name, Tower number, tower name, SIM card number ,"&_
"Last (number of lightning strikes on Phase A) Total number of lightning strikes on Phase ,"&_
"Last (number of lightning strikes in Phase B) as total number of lightning strikes in Phase B ,"&_
"Last (number of lightning strikes in Phase C) Total number of lightning strikes in Phase C "&_
"From view _ total number of tower lightning strikes "&_
"Group by number, line name, Tower number, tower name, SIM card number "&_
"Order by number, line name, Tower number, tower name, SIM card number"

 

 

Summary:

1. Use the ccess View Design (1). The [time] field is the second column. When the last function is used for grouping, the [total number of lightning strikes] will be sorted by date.

2. Use the ccess View Design (2). The [time] field is the end column. When grouping and using the last function to query, the [total number of lightning strikes] will be in the order of [total number of lightning strikes of Phase. This field is located in front of the column order.

 

If no sorting is specified for an SQL query, the values of fields are prioritized.

 

 

 

 

 

Author: Duan Liqing QQ: 14035344