CODEWITHSUNDEEP
sqlsql-server
Bruno Gomes
Bruno Gomes

Reputation: 125

Select distinct with where clause and most recent date

I have a table where I need to find only the different ARM field values and STK respective, where DATE is the most recent and STK is greater than 1. That is, if the most recent negative I no longer want you to show.

The table is as follows:

Table MOV         

   Id       ARM   STK       DATE             
b3c842e3    F4    230   23-03-2019 00:00:00
b3c842e4    P8    832   24-03-2019 00:00:00
b32d4211    F4    -30   21-03-2019 00:00:00
6hhd421q    F4    100   26-03-2019 00:00:00
kih3221a    P8    -12   25-03-2019 00:00:00

The result I want is this:

ARM   STK
F4    100

I tried to use SELECT DISTINCT but I could not do what I wanted because of WHERE.

Upvotes: 0

Views: 2275

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I am not sure if you want the most recent value with stk > 1:

select top (1) m.*
from mov m
where m.stk > 1
order by m.date desc;

Or, if you want rows where the most recent value for an ARM has stk > 1. If this is what you want, then:

select m.*
from (select m.*,
             row_number() over (partition by arm order by date desc) as seqnum
      from mov m
     ) m
where seqnum = 1 and m.stk > 1
order by m.date desc;

Both of these return the one row you have specified.

Here is a db<>fiddle showing that this does exactly what you specify in the question (well, it returns the full row, not just two columns).

Upvotes: 0

forpas
forpas

Reputation: 164099

You can group by arm to get all the max dates for each arm, then join to the table mov and reject all the rows for which the condition stk > 1 does not apply.

select 
  m.arm, m.stk 
from mov m inner join (
  select arm, max(date) date
  from mov
  group by arm
) g on g.arm = m.arm and g.date = m.date
where m.stk > 1

See the demo.
Results:

> arm | stk
> :-- | --:
> F4  | 100

Upvotes: 0

KVerwold
KVerwold

Reputation: 261

You could use this SQL to get your wanted result.

select t1.ARM,t1.STK from MOV t1
join (select Date=MAX(DATE) from MOV where STK > 1) T2 on t1.Date = T2.Date

but be aware, if multiple records have the same DATE, multiple lines would be returned.

Upvotes: 0

Gen Wan
Gen Wan

Reputation: 2009

Try this:

select top 1 arm, stk
from mov
where stk > 1
group by arm,STK
order by max(date) desc

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You can use row_number() to assingn each row a number that is 1 for the largest date for all arm each and filter on that.

SELECT x.id,
       x.arm,
       x.stk,
       x.date
       FROM (SELECT t.id,
                    t.arm,
                    t.stk,
                    t.date,
                    row_number() OVER (PARTITION BY arm
                                       ORDER BY date DESC)
                    FROM elbal t
                    WHERE t.stk > 1) x
       WHERE x.rn = 1;

Upvotes: 1

Related Questions