How to find minimum values in a column in sql

Question

If I have a table like this:

id   name   value
1    abc      1
2    def      4
3    ghi      1
4    jkl      2

How can I select a new table that still has id, name, value but only the ones with a minimum value.

In this example I need this table back:

1  abc 1
3  ghi 1

Answer 1

Finding those values is pretty straightforward:

SELECT *
FROM YourTable
WHERE value = (SELECT MIN(Value) FROM YourTable);

As for the right syntax for putting those rows in another table, that will depend on the database engine that you are using.

Answer 2

An alternative to @Lamak's solution could be to use the rank window function. Depending on the exact scenario, it may perform quite better:

SELECT id, name, value
FROM   (SELECT id, name, value, RANK() OVER (ORDER BY value ASC) AS rk
        FROM   mytable) t
WHERE  rk = 1

Answer 3

not sure exactly if this is what you're trying to do, but I think this would work:

    --creating #temp1 to recreate your table/example
     CREATE TABLE #TEMP1
    (id      INT       NOT NULL PRIMARY KEY, 
     name    CHAR(3)   NOT NULL,
     value   INT       NOT NULL)


    INSERT INTO #TEMP1
    VALUES 
    (1,'abc',1), 
    (2,'def',4), 
    (3,'ghi',1), 
    (4,'jkl',2)

    -verify correct

    SELECT * FROM #temp1

    --populate new table with min value from table 1

    SELECT *
    INTO #TEMP2
    FROM #TEMP1
    WHERE value = (SELECT MIN(value) 
                   FROM #TEMP1)

    SELECT * FROM #TEMP2