SQL statements for obtaining the maximum and minimum values

SQL statements for obtaining the maximum and minimum values

SQL statements for obtaining the maximum and minimum values

If object_id ('tb') is not null drop table tb;
Go
Create table tb (
Col1 int,
Col2 int,
Fcount int)
Insert into tb
Select 11,20, 1 union all
Select 11,22, 1 union all
Select 11,23, 2 union all
Select 11,24, 5 union all
Select 12, 39, 1 union all
Select 12, 40, 3 union all
Select 12, 38, 4
Go
-- Query
-- 1
Select * from tb t where fcount = (select max (fcount) from tb where col1 = t. col1)
-- 2
Select * from tb t where not exists (select 1 from tb where col1 = t. col1 and fcount> t. fcount)
-- Result
/*
Col1 col2 fcount
---------------------------------
12 38 4
11 24 5

*/

Instance 2

There are n kinds of goods in the goods purchase orders table, each of which corresponds to the quotation of m suppliers. I want to get this result: from a table, we can see at a glance the lowest seller price and the highest seller price for each product. How can we do this?

1) Create a database tutorial test. mdb
2) import the data tables of info. mdb and info1.mdb, which are info and info1 respectively. The table data is as follows:

Info
-----
Product Name
1234
2345

Info1
---------
Product Name vendor price
1234 vendors 1 10
1234 manufacturers 2 20
1234 manufacturers 3 30
2345 manufacturers 1 40
2345 manufacturers 2 50
2345 manufacturers 3 60
3456 manufacturers 1 70
3456 manufacturers 2 80
3456 manufacturers 3 90

3) create a new query and open the SQL view (View> SQL view)

4) paste the following content and run

Select info1. name, max (info1. price) as price maximum, min (info1. price) as price minimum
From info1
Group by info1.
Having info1. name in (select info. name from info );

5) The running result is as follows:

Query 1: SELECT query
------------
Product Name price-maximum price-minimum
1234 30 10
2345 60 40