SQL Select Rows with Max and Min Date

SQL Select rows with max and min date

you can use this:

select * from dbo.myDatabase 
where 
    ([Date] = (select max([Date]) from /* your query */ ) or 
    [Date] = (select min([Date]) from /* your query */ ))
    and MachineId = '7B788EE88E-6527-4CB4-AA4D-01B7F4048559' -- or any other id

Edit: since it's entirely possible that two machines have the same date value, the query should be updated to also include a MachineId filter in the where clause. I updated the query to show this.

Selecting the MIN(date) and the MAX(date) with the MAX(date) Freight Values

Just use conditional aggregation:

SELECT o.customerID,
      MAX(o.OrderDate) AS LastOrder,
      MIN(o.OrderDate) AS FirstOrder,
      MAX(CASE WHEN seqnum = 1 THEN o.freight END) as lastFreight
FROM (SELECT o.*,
             ROW_NUMBER() OVER (PARTITION BY o.customerID ORDER BY o.OrderDate DESC) as seqnum
      FROM Orders o
     ) o
GROUP BY o.CustomerID;

Note that you do not appear to need the Customer table. All the information you need is in Orders (I doubt you are using Customer to remove rows.)

Pass min and max date from table to select query

Your query is basically fine. The problem is the where clause. That condition should be on the first table:

select *
from (select  null as ID, (select min(date_of_rate) from currency_rate) + ROWNUM - 1 as DATE_OF_RATE, null as VALUE, null as CURRENCY_ID
      from dual
      connect by level <= (select max(date_of_rate) - min(date_of_rate) + 1 from currency_rate)
     ) empCur left join
     CURRENCY_RATE cr
     on empCur.DATE_OF_RATE = cr.DATE_OF_RATE and
        cr.currency_id = 4
where empCur.DATE_OF_RATE >= date '2015-08-28' and
      empCur.DATE_OF_RATE <= date '2015-09-05' ;

I don't see why TRUNC() is necessary. If it is, just add it back in.

selecting rows with max and min date on the same select

One way to do this (which might not be the best) is to use correlated subqueries.

select 
  id, 
  name,
  (select position 
   from employees e1 
   where e1.id = e.id 
   and not exists (select 1 
                   from employees 
                   where id = e1.id
                   and hired_date > e1.hired_date
                  )
  ) as position,
   min(hired_date) hired_date
from employees e
group by id, name;

The outer subquery gets the position for the employee for which there doesn't exists any row with a later date.

Sample SQL Fiddle

This query returns:

|   Id | Name | position |              hired_date |
|------|------|----------|-------------------------|
| 1035 | Alex | Asst.Mgr | April, 01 1995 00:00:00 |
| 1177 | Ryan |  Ops.Mgr |  July, 23 1996 00:00:00 |

In response to a comment... if your employees can have multiple positions with the same latest date you can use either of these queries to get all positions at the latest date:

select 
  e.id, 
  e.name,
  min(e.hired_date) hired_date,
  pos.position
from employees e
join (
  select id, position 
  from employees e1 
  where not exists (select 1 
                    from employees 
                    where id = e1.id
                    and hired_date > e1.hired_date
                   )
) as pos on e.id = pos.id
group by e.id, e.name, pos.position;

select 
  e.id, 
  e.name,
  min(e.hired_date) hired_date,
  group_concat(distinct pos.position) as position
from employees e
join (
  select id, position 
  from employees e1 
  where not exists (select 1 
                    from employees 
                    where id = e1.id
                    and hired_date > e1.hired_date
                   )
) as pos on e.id = pos.id
group by e.id, e.name;

If we add a position at the latest date for Alex then these queries would return an output like:

|   Id | Name |              hired_date | position |
|------|------|-------------------------|----------|
| 1035 | Alex | April, 01 1995 00:00:00 | Asst.Mgr |
| 1035 | Alex | April, 01 1995 00:00:00 |      Mgr |
| 1177 | Ryan |  July, 23 1996 00:00:00 |  Ops.Mgr |

|   Id | Name |              hired_date |     position |
|------|------|-------------------------|--------------|
| 1035 | Alex | April, 01 1995 00:00:00 | Asst.Mgr,Mgr |
| 1177 | Ryan |  July, 23 1996 00:00:00 |      Ops.Mgr |

Sample SQL Fiddle for the extra queries

MySQL How to select a date record between the min and max

BETWEEN includes the end points. Presumably, you want to exclude them:

SELECT R.*
FROM RUBERIC R  
WHERE R.SCHOOLID = 75 AND
      R.OBSERVED = 'Observed Classroom' AND
      R.DATE > (SELECT MIN(R2.DATE) FROM RUBERIC R2 WHERE R2.SCHOOLID = 75 
AND R2.TEACHERID = 610 AND R2.OBSERVED = 'Observed Classroom'
               ) AND
      R.DATE < (SELECT MAX(R2.DATE) FROM RUBERIC R2 WHERE R2.SCHOOLID = 75 
AND R2.TEACHERID = 610 AND R2.OBSERVED = 'Observed Classroom'
               );

Also, your query is -- inadvertently -- a correlated subquery. The table alias R refers to the rows in the main query, not the subquery. You should always define table aliases and always use them, but use them properly. (Note all the R2s in the subqueries in this version.)

In general, I strongly discourage anyone from using BETWEEN with date or date/times. This is not a problem in your case, but the time component can lead to unexpected results.

Select only rows with max date

Your query returns what you need - only one row for each _id where column _status_set_at has its max value.
You do not need to change anything in your original query.

count(_id) shows how many rows for each _id in the original table, but not in a query result.
Query result has only one row for each _id because you group by _id.

This query shows that in your query result there is only one row for each _id

SELECT _id, max_status_set_at, count(_id) FROM (
SELECT _id, max(_status_set_at) max_status_set_at
FROM pikta.candidates_states
GROUP BY _id) t
GROUP BY _id

If you need apply a condition on max(_status_set_at) you can use HAVING

SQL Server: SELECT only the rows with MAX(DATE)

If rownumber() over(...) is available for you ....

select OrderNO,
       PartCode,
       Quantity
from (select OrderNO,
             PartCode,
             Quantity,
             row_number() over(partition by OrderNO order by DateEntered desc) as rn
      from YourTable) as T
where rn = 1      

How to select the min and max date from a table into another table on the same row? in SQL

You could try joining to grouped table:

select
    grouped.ID,
    grouped.AdmissionDate,
    grouped.DischargeDate,
    admission.Category admissionCategory,
    discharge.Category dischargeCategory
from 
(
    select
        ID,
        min(AdmissionDate) AdmissionDate,
        max(DischargeDate) DischargeDate
    from testTable
    group by ID
) [grouped]
left join testTable [admission] on
    [grouped].ID = [admission].ID and 
    [grouped].AdmissionDate = [admission].AdmissionDate
left join testTable [discharge] on
    [grouped].ID = [discharge].ID and 
    [grouped].DischargeDate = [discharge].DischargeDate

Database fiddle.

---

Related Topics