SQL Selecting Rows by Most Recent Date with Two Unique Columns

SQL selecting rows by most recent date with two unique columns

You can use a GROUP BY to group items by type and id. Then you can use the MAX() Aggregate function to get the most recent service month. The below returns a result set with ChargeId, ChargeType, and MostRecentServiceMonth

SELECT
  CHARGEID,
  CHARGETYPE,
  MAX(SERVICEMONTH) AS "MostRecentServiceMonth"
FROM INVOICE
GROUP BY CHARGEID, CHARGETYPE

How do I select the rows with the most recent date in SQL?

Perhaps you seems want :

select d.employee_id, d.status, d.update_date 
from data d
where update_date = (select max(d1.update_date) 
                     from data d1 
                     where d1.employee_id = d.employee_id
                    );

SQL selecting rows by most recent date with one unique column (selecting 3+ columns)

The classic way to do this and still very useful in this case:

select 
  ean, 
  price,
  dtmod
from my_table t0
where
  t0.dtmod = (
    select max(dtmod) 
    from my_table t1 
    where t1.ean = t0.ean
  )
order by ean
;

Also, super helpful for everyone is to know how to use the window functions, any SQL programmer should spend some time to understand all of the window functions.

with
w_o as (
  select
  ean,
  price,
  dtmod,
  row_number() OVER (PARTITION BY ean ORDER BY dtmod DESC) rn
  from my_table
)
select
  ean,
  price,
  dtmod
from w_o
where rn = 1
order by ean
;

Note:

For testing, I added a duplicate record with same EAN, and same timestamp to the test data. The first query gives both the second gives only one. You can determine which one it returns by tuning the ordering.

Edit 1.
I forgot the order by. :)

Edit 2.
Added window function information.

http://sqlfiddle.com/#!17/8278a/1/1

Selecting most recent date between two columns

CASE is IMHO your best option:

SELECT ID,
       CASE WHEN Date1 > Date2 THEN Date1
            ELSE Date2
       END AS MostRecentDate
FROM Table

---

If one of the columns is nullable just need to enclose in COALESCE:

.. COALESCE(Date1, '1/1/1973') > COALESCE(Date2, '1/1/1973')

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime

SQL Group two columns and select recent date/timestamp

Just to confirm

• For each 'pair' of callers (one of who is "22")
• You would like to get the latest text which went either way (either to or from 22)

The answer here uses the following approach

• For each row, determine the other party (this is called second_character_id in the query)
• For each other party, order the 'text' rows based on the date sent
• Get the latest row for each party

WITH Call_list AS
    (SELECT *,
        @character_id AS `primary_character_id`,
        CASE 
            WHEN `character_id` = @character_id THEN `target_character_id`
            ELSE `character_id` END AS `second_character_id`
    FROM
        `phone_messages`
    WHERE
        (`character_id`= @character_id OR `target_character_id`= @character_id)
        AND (`type`= @t )
    ),
Sorted_Call_List AS
    (SELECT *,
            ROW_NUMBER() OVER 
                (PARTITION BY `primary_character_id`, `second_character_id` 
                ORDER BY `Date` DESC
                ) AS char_rn
    FROM Call_list
    )
SELECT `character_id`, `target_character_id`, `message`, `type`, `date`
FROM   Sorted_Call_List
WHERE  char_rn = 1;

With this answer I also included the primary_character_id - which in this case is always 22 - in case you wanted to expand it to have multiple people.

Here is a db<>fiddle with the data setup and approach.

Note that there is an additional query at the bottom there - that was my previous attempt where I misunderstood the requirements. In that answer, it finds the latest 'incoming' and latest 'outgoing' text to/from 22, regardless of who they came from.

SQL: Selecting multiple fields with one unique field based on most recent date

I would suggest a correlated subquery:

SELECT t.*
FROM Table as t
WHERE t.ReceiveDate = (SELECT MAX(t2.ReceiveDate)
                       FROM Table as t2
                       WHERE t2.PartNo = t.PartNo
                      );

In particular, this can take advantage of an index on (PartNo, ReceiveDate).

SQL selecting unique key field with most recent date/time

You can use the MAX() and GROUP BY in SQL to get that.

SELECT POINTID,MAX(VALUE)
FROM table
GROUP BY POINTID

---

Related Topics