Select largest date from column based on another column in table

Question

I'm new to SQL. Trying to get a certain date for jobs from a table. The only way to get these dates is to look to a massive table where every item for each job is stored with a last transaction date. The date I want is the largest date in the lst_trx_date column for each job.

The data in the table looks something like this:

Where each job has a varying amount of items. My biggest hurdle and my main question: How can I instead of selecting the entire job table only select the largest lst_trx_date for each job? I initially brought in the data using microsoft query, but I realize my request will probably require modifying the SQL command text directly.

Answer 1

I think it would be along these lines:

SELECT job, MAX(lst_trx_date) as job, last_transaction_date
FROM table
GROUP BY job
ORDER BY lst_trx_date DESC

Answer 2

Try something like this.. this will give you the max date

SELECT MAX (lst_trx_date) AS "Max Date" 
FROM table where job = 1234;

Answer 3

To get the latest date for each job, you can use windowing functions. As an example try:

    select job, item, lst_trx_date from (select job, item, lst_trx_date, row_number() 
                                         over(partition by stat,job,item order by 
                                         lst_trx_date desc) rn
                                        from <table>)t
    where rn = 1