Select Record with Minimum Value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Record with Minimum Value

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-03-22 : 14:23:37

working with table named iminvtrx

Data looks like this:


item_no   old_qty   doc_dt         ID
 ABC         5      01/12/2012     123
 ABC         2      03/23/2012     124
 ABC         44     03/03/3011     125

What I want to do is select the item_no and old Qty where the ID is the minimum.

I would like my select statement to return
ABC 5 01/12/2012
using the data above. I do not want to see the other two records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:04:00

lots of ways

sql 2000 or earlier supported


SELECT t.item_no,   t.old_qty,   t.doc_dt,         t.ID
FROM table t
INNER JOIN (SELECT item_no.MIN(ID) AS MinID
            FROM table
            GROUP BY item_no
           )t1
ON t1.item_no = t.item_no
AND t1.MinID = t.ID

sql 2005 based solutions

using ROW_NUMBER function


SELECT item_no,
old_qty,
doc_dt,
ID
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY item_no ORDER BY ID) AS Rn
FROM Table
)t
WHERE Rn=1


using APPLY operator
SELECT t.item_no,
t.old_qty,
t.doc_dt,
t.ID
FROM table t
CROSS APPLY (SELECT MIN(ID) AS MinID
             FROM table
             WHERE item_no = t.item_no
            )t1
WHERE t1.MinID = t.ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources