how to find the Min value among multiple col
i have in my DB 3 col and i want to find a single value among all of them as explaind here: table name:MyTable
开发者_运维知识库-----------------------------
--id-- col1-- col2-- col3-
-----------------------------
1 200 300 400
2 100 150 300
3 800 102 20
4 80 80 0
i want the result out of col1 col2 col3 to be = 0 , which is the min value among them. is it possible !!!!
my try:
select Min(col1, col2 , col3) as Tablemin
from MyTable
ANSI SQL:
select min(col)
from
(
select col1 [col] from MyTable
union all
select col2 from MyTable
union all
select col3 from MyTable
)t
If it is MySQL or Oracle, there is LEAST()
function:
SELECT LEAST(MIN(col1), MIN(col2), MIN(col3))
AS MinOfAllColumns
FROM MyTable
Then, there is the CASE
statement which can be used in most RDBMSs:
SELECT CASE WHEN MIN(col1) <= MIN(col2) AND MIN(col1) <= MIN(col3)
THEN MIN(col1)
WHEN MIN(col2) <= MIN(col3)
THEN MIN(col2)
ELSE MIN(col3)
END
AS MinOfAllColumns
FROM MyTable
This can also work but it's hard to get other fields with the UNION
approach:
SELECT MIN(col) AS MinOfAllColumns
FROM
( SELECT MIN(col1) AS col
FROM MyTable
UNION
SELECT MIN(col2)
FROM MyTable
UNION
SELECT MIN(col3)
FROM MyTable
) AS tmp
Simple way to display min is:
string val = textBox2.Text;
DataSet ds;
con.Open();
string str = "SELECT * FROM Add_Rooms WHERE Price >= @price";
cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue("@price", val);
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Add_Rooms");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Add_Rooms";
con.Close();
In SQLITE the answer is as simple as:
SELECT MIN(MIN(col1), MIN(col2), MIN(col3)) FROM MyTable;
精彩评论