Select to Get Rows Based on Minimum Value of a Column

Selecting rows based on minimum value of one column

You could also use top (1) with ties

select top (1) with ties a, * 
from table t
order by row_number() over (partition by a order by [Order])

SQL Select only rows with Minimum Value on a Column with Where Condition

You can start by selecting the minimum orderIndex of products that are not rejected like this:

SELECT productId, MIN(orderIndex)
FROM myTable
WHERE rejected = 0
GROUP BY productId;

Once you have that, you can join it with your original table on the condition that productId and minOrderIndex match:

SELECT m.id, m.productId, m.orderIndex
FROM myTable m
JOIN(
  SELECT productId, MIN(orderIndex) AS minOrderIndex
  FROM myTable
  WHERE rejected = 0
  GROUP BY productId) tmp ON tmp.productId = m.productId AND tmp.minOrderIndex = m.orderIndex;

My query makes the assumption that there are no duplicate (productId, orderIndex) pairs. As long as those don't exist, this will work just fine. Here is an SQL Fiddle example.

SQL query to select distinct row with minimum value

Use:

SELECT tbl.*
FROM TableName tbl
  INNER JOIN
  (
    SELECT Id, MIN(Point) MinPoint
    FROM TableName
    GROUP BY Id
  ) tbl1
  ON tbl1.id = tbl.id
WHERE tbl1.MinPoint = tbl.Point

Select Rows with min value for each group

But the best would be save dates as date column. The you can use all function for dates

CREATE TABLE table1 (
  [Date]  varchar(10),
  [Container ID] INTEGER
);

INSERT INTO table1
  ([Date], [Container ID])
VALUES
  ('1/1', '1'),
  ('2/2', '1'),
  ('3/3', '1'),
  ('4/4', '2'),
  ('5/5', '2'),
  ('6/6', '3'),
  ('7/7', '3');
GO

SELECT MIN([Date]), [Container ID] FROM table1 GROUP BY [Container ID]
GO

(No column name) | Container ID
:--------------- | -----------:
1/1              |            1
4/4              |            2
6/6              |            3

db<>fiddle here

Select a pandas dataframe row where column has minimum value

A column name of df.S works but a column name of df.T doesn't work. df.T invokes the Transpose operation because it takes namespace precedence. Here's @ansev answer using literals instead.

df[df['S']==df['S'].min()]

Pandas GroupBy and select rows with the minimum value in a specific column

I feel like you're overthinking this. Just use groupby and idxmin:

df.loc[df.groupby('A').B.idxmin()]

   A  B   C
2  1  2  10
4  2  4   4

---

df.loc[df.groupby('A').B.idxmin()].reset_index(drop=True)

   A  B   C
0  1  2  10
1  2  4   4

pandas groupby ID and select row with minimal value of specific columns

Bkeesey's answer looks like it almost got you to your solution. I added one more step to get the overall minimum for each group.

import pandas as pd

# create sample df
df = pd.DataFrame({'ID': [1, 1, 2, 2, 3, 3], 
               'A': [30, 14, 100, 67, 1, 20], 
               'B': [10, 1, 2, 5, 100, 3],
               'C': [1, 2, 3, 4, 5, 6],
               })

# set "ID" as the index
df = df.set_index('ID')

# get the min for each column
mindf = df[['A','B']].groupby('ID').transform('min')

# get the min between columns and add it to df
df['min'] = mindf.apply(min, axis=1)

# filter df for when A or B matches the min
df2 = df.loc[(df['A'] == df['min']) | (df['B'] == df['min'])]

print(df2)

In my simplified example, I'm just finding the minimum between columns A and B. Here's the output:

      A    B  C  min
ID
1    14    1  2    1
2   100    2  3    2
3     1  100  5    1

---

Related Topics