SQL Query to Select Distinct Row with Minimum Value

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

how to select distinct row with minimum value

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by ig_team order by ig_idx asc) as seqnum
      from t
     ) t
where seqnum = 1;

SQL query to select distinct row with minimum value from two table

For Sql Server you can use OUTER APPLY:

select * from t1
outer apply(select top 1 * from t2 where t1_id = t1.id order by price) oa

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.

row with minimum value of a column

Try this -

 select top 1 * from table where N_UM = (select min(N_UM) from table);

Group by minimum value in one field while selecting distinct rows

How about something like:

SELECT mt.*     
FROM MyTable mt INNER JOIN
    (
        SELECT id, MIN(record_date) AS MinDate
        FROM MyTable
        GROUP BY id
    ) t ON mt.id = t.id AND mt.record_date = t.MinDate

This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates is if there are duplicate minimum record_dates for the same ID.

SQL select distinct min and max records from date and lowest and highest values

I'm using mySql 5.6 and this query works:

select date(m.min_max_date) as date,
   max(case when m.lbl='min_hr_price' then m.min_max_hr_price else null end) as lowest_hr_price,
   max(case when m.lbl='max_hr_price' then m.min_max_hr_price else null end) as max_hr_price,
   max(case when n.lbl='min_price' then n.min_max_price else null end) as min_price,
   max(case when n.lbl='max_price' then n.min_max_price else null end) as max_price
from (select 'min_hr_price' as lbl, price as min_max_hr_price, date as min_max_date 
  from tbl 
  where date in (select min(date) as min_date from tbl group by date(date))
union 
select 'max_hr_price', price, date   
  from tbl 
  where date in (select max(date) as max_date from tbl group by date(date))) as m,
(
select 'min_price' as lbl,
min(date) as min_max_date,
min(price) as min_max_price
from tbl
group by date(date)
union
select 'max_price' as lbl,
max(date) as min_max_date,
max(price) as min_max_price
from tbl
group by date(date)
) n
where m.min_max_date=n.min_max_date
group by date(m.min_max_date)
order by m.min_max_date

Sample result:
date        lowest_hr_price max_hr_price    min_price   max_price
2018-03-06  $1102.8         $1821           $1011.6     $1821

INSERT INTO TBL VALUES(1, '2018-03-06 22:19:10', '$1011.6');
INSERT INTO TBL VALUES(2, '2018-03-06 13:19:11', '$1011.6');
INSERT INTO TBL VALUES(3, '2018-03-06 03:21:25', '$1106.2');
INSERT INTO TBL VALUES(4, '2018-03-06 00:26:50', '$1102.8');
INSERT INTO TBL VALUES(5, '2018-03-06 22:26:17', '$1821');

---

Related Topics