MIN in a where statement

1

Hello,

In the database I am using there is a table which ranks records, this is really useful as you can select the most recent one by saying 'seq_rev = 1'. However, there are times where I don't want the most recent one but I want 'seq_rev = 2' because that is the first record with a certain status.

Is there a way that I could use MIN in a where clause for this?

For example, my where clause would say something like "where tag = 'Offer' and MIN(seq_rev)".

Does anyone have any ideas?

Thanks in advance!

2

Try
HAVING MIN(seq_rev) = 2

3

Thanks but the number might not be 2 necessarily, it just will be the highest that fits the other criteria.

4 
SELECT ...column_list...
FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY seq_rev) AS row_num
    FROM dbo.your_table_name
    WHERE tag = 'Offer'
) AS derived
WHERE row_num = 1
1 Like
5

Thanks Scott, just got the email and was going to do something like that.

6

Thanks for all of your help, I think I must be doing something wrong as when I test my script for one particular case it isn't showing any results and I suspect the reason is because row_num 1 doesn't fit the other criteria.

Could this be the case?

select * from (
select
sth.client_id,
sth.crms_number,
sth.tag_value,
sth.date_created [DateTagCreated],
ROW_NUMBER() OVER (ORDER BY seq_rev) AS row_num
from dbo.zz_crms_student_tag_history sth
where sth.tag_value LIKE '%Jan%'
and sth.tag_value LIKE '%17%') as derived where row_num = 1

7

So when you run

    select
        sth.client_id,
        sth.crms_number,
        sth.tag_value,
        sth.date_created [DateTagCreated],
        ROW_NUMBER() OVER (ORDER BY seq_rev) AS row_num
    from dbo.zz_crms_student_tag_history sth
    where sth.tag_value LIKE '%Jan%'
        and sth.tag_value LIKE '%17%'

what do you get?

8

Hello,

When I run that script, I get records which fit the criteria numbered from 1 to 1,848. This does contain duplicates obviously at this stage whereas I want the first time a record fits the criteria - not the last or all of them.

Thanks in advance

9 
I want the first time a record fits the criteria
[/quote]

Does that have [row_num] = 1 in @djj55 query above?

If not, what are the column values for the row you want? (you probably need to add seq_rev to the SELECT so you can see what value you are getting for that column too)
10

Thanks Kristen,

I need to see the first time that the tag_value fits the criteria

where sth.tag_value LIKE '%Jan%' and sth.tag_value LIKE '%17%'

for each client_id and crms_number (basically these two combined are unique but crms_number alone is not unique).

Does that make sense?

11

Sample data and expected results would help.

12

Thanks Kristen, I understand.

I hope this helps, I really appreciate your help!

13

Add a PARTITION BY on the client_id column to the ROW_NUMBER()

ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY seq_rev) AS row_num