How to Select the Oldest Date With Ties

How to select the oldest date with ties?

Use a CTE to return all the children of the person ('Michael Fox') and then NOT EXISTS:

with children as (
  select p.fname, p.lname, p.bdate
  from persons p inner join births b
  on p.fname = b.fname and p.lname =b.lname
  where (b.fthr_fname = 'Michael' and b.fthr_lname = 'Fox') 
  or (b.mthr_fname = 'Michael' and b.mthr_lname = 'Fox')
)
select c.* from children c
where not exists (
  select 1 from children
  where bdate < c.bdate
) 

See the demo.

Results:

| fname   | lname | bdate      |
| ------- | ----- | ---------- |
| TwinOne | Fox   | 1995-11-20 |
| TwinTwo | Fox   | 1995-11-20 |

how to select the oldest or newest date using where?

This is possible(ANSI SQL)

SELECT * FROM employees 
WHERE birth_date = (select MIN(birth_date) from employees)

or You can use TOP 1 with Ties(SQL SERVER)

Select TOP 1 with TIES * 
FROM employees 
Order by birth_date ASC

How to select oldest date row from each product using SQL

You can do it with a Cross Apply, this would be your SQL query:

Select  P.IDProizvoda,
        P.NazivProizvoda,
        N.DatumKupovine,
        N.NaLageru,
        N.IDKupovine,
        N.CenaPoKomadu
        From 
products P
Cross Apply
(
    Select top 1    DatumKupovine,
                    NaLageru,
                    IDKupovine,
                    CenaPoKomadu
    From products P2
    where P2.IDProizvoda = P.IDProizvoda
    and P2.NaLageru > 0 
    order by DatumKupovine
)  N
group by P.IDProizvoda, P.NazivProizvoda, N.DatumKupovine, N.NaLageru, N.IDKupovine, N.CenaPoKomadu

And this your ExeQuery:

Access.ExeQuery("Select P.IDProizvoda, P.NazivProizvoda, N.DatumKupovine, N.NaLageru, N.IDKupovine, N.CenaPoKomadu From products P " &
        " Cross Apply( Select top 1 DatumKupovine, NaLageru, IDKupovine, CenaPoKomadu From products P2 where P2.IDProizvoda = P.IDProizvoda and P2.NaLageru > 0 order by DatumKupovine)  N " &
        " where P.IDProizvoda in (Select value From STRING_SPLIT(@listofproduct, ',')) " &
        " group by P.IDProizvoda,   P.NazivProizvoda, N.DatumKupovine, N.NaLageru, N.IDKupovine, N.CenaPoKomadu " )

How do I find the oldest date in Group

Since you want to keep the ties, I'd do it like this:

SELECT t2.AssignedProfsHistory, m.MatterID, t2.EffectiveDate
FROM (
   SELECT MatterID, MIN(EffectiveDate) med 
   FROM AssignedProfsHistory 
   WHERE AssignedType = 'Originating'
   GROUP BY MatterID
) t1
INNER JOIN AssignedProfsHistory t2 ON t2.MatterID = t1.MatterID 
   and t2.EffectiveDate = t1.med and t2.AssignedType = 'Originating'
INNER JOIN Matters m on m.Matters = t2.Matters
ORDER BY m.MatterId

Here is an SQLFiddle without the Matters table that demonstrates it can work, no windowing functions or CTE required, though a CTE would allow you to avoid repeating the AssignedType='Originating' condition.

Get earliest date with group by- SQL

Another option is using the WITH TIES clause in concert with Row_Number()

Select Top 1 with ties *
 From  dbo.Accounts
 Order By Row_Number() over (Partition By IdentityNumber Order By CreatedDate)

How do you select the value for the latest date?

If you want just one row, use top (1) and order by:

select top (1) *
from status s
where invalid = 0 and id = 16888
order by valTimestamp desc

If you want the same result over multiple ids, then one option uses window functions:

select *
from (
    select s.*, row_number() over(partition by id order by valTimestamp desc) rn
    from status s
    where invalid = 0
) s
where rn = 1

If you want to allow ties, then you would use top (1) with ties in the first query, and rank() instead of row_number() in the second query.

Get Earliest Date corresponding to the latest occurrence of a recurring name

This is a type of gaps-and-islands problem.

There are many solutions. Here is one that is optimized for your case

• Use LEAD/LAG to identify the first row in each grouping
• Filter to only those rows
• Number them rows and take the first one

WITH StartPoints AS (
    SELECT *,
      IsStart = CASE WHEN Name <> LEAD(Name, 1, '') OVER (ORDER BY Date DESC) THEN 1 END
    FROM YourTable
),
Numbered AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC)
    FROM StartPoints
    WHERE IsStart = 1 AND Name = 'X'
)
SELECT
  Name, Date
FROM Numbered
WHERE rn = 1;

db<>fiddle

For SQL Server 2008 or earlier (which I strongly suggest you upgrade from), you can use a self-join with row-numbering to simulate LEAD/LAG

WITH RowNumbered AS (
    SELECT *,
      AllRn = ROW_NUMBER() OVER (ORDER BY Date ASC)
    FROM YourTable
),
StartPoints AS (
    SELECT r1.*,
      IsStart = CASE WHEN r1.Name <> ISNULL(r2.Name, '') THEN 1 END
    FROM RowNumbered r1
    LEFT JOIN RowNumbered r2 ON r2.AllRn = r1.AllRn - 1
),
Numbered AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC)
    FROM StartPoints
    WHERE IsStart = 1
)
SELECT
  Name, Date
FROM Numbered
WHERE rn = 1;

T-SQL select rows by oldest date and unique category

Have a look at row_number() on MSDN.

SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY date_time, id) rn
        FROM    mytable
        ) q
WHERE   rn = 1

(run the code on SQL Fiddle)

---

Related Topics