CODEWITHSUNDEEP
sqlsubquerymin
user2283253
user2283253

Reputation: 1

sql min() in where

I want to find those cases where an "rsp_ver" starts with 2.0 or more instead of 1.0. I figured I could use a conditional in the where clause to find out the IDs with rsp_ver codes over "1.0" found by a subquery:

select 
  trim(INS.fst_name) || '  ' || Trim(INS.lst_name) as name,
  IDS.rin,
  RSP.unit_id,
  RSP.indv_id,
  RSP.rsp_id,
  RSP.rsp_sqnm,
  RSP.rsp_ver,
  cast(RSP.post_ts as char(16)) as Post,
  RSP.post_audit_id
from 
  dhsdb2.wvs_rsp RSP
  join dhsdb2.indv_name_srch INS on RSP.indv_id = INS.indv_id
  join dhsdb2.indv_demg_srch IDS on RSP.indv_id = IDS.indv_id
where 
  RSP.indv_id in 
    (select 
       SRSP.indv_id 
     from 
       dhsdb2.wvs_rsp SRSP 
     where 
       SRSP.indv_id = RSP.indv_id and
       SRSP.rsp_ver > '1.0')
  and rsp.clse_ts is null 
order by
  RSP.rsp_ver;

This didn't work of course. Then I thought I needed that inner query to have the min function, but I ran int

Upvotes: 0

Views: 155

Answers (2)

Jon Senchyna
Jon Senchyna

Reputation: 8037

Assuming that rsp_ver is always in the format of [Major Version].[Other Version Info], you could replace the nested select in your where clause:

RSP.indv_id in 
    (select 
       SRSP.indv_id 
     from 
       dhsdb2.wvs_rsp SRSP 
     where 
       SRSP.indv_id = RSP.indv_id and
       SRSP.rsp_ver > '1.0')

with the following:

CAST(SUBSTRING(RSP.rsp_ver, 1, CHARINDEX('.', RSP.rsp_ver)-1) AS INT) >= 2

This gets the major version portion from your rsp_var column, converts it to an INT, and then returns only the rows whose major version is 2 or higher.

This has a benefit over simply casting rsp_ver to a numeric value when you have version numbers with more than one decimal (i.e. 2.0.1). The downside is that it only checks the major version in its current form.

If you need to get more granular on your filtering, you could create a Common Table Expression that selects the different version parts from the rsp_ver column, along with indv_id, and then join on that query.

Upvotes: 1

What have you tried
What have you tried

Reputation: 11138

Remove the ' from the Where clause in order to compare the column as a number rather than a string

select 
  trim(INS.fst_name) || '  ' || Trim(INS.lst_name) as name,
  IDS.rin,
  RSP.unit_id,
  RSP.indv_id,
  RSP.rsp_id,
  RSP.rsp_sqnm,
  RSP.rsp_ver,
  cast(RSP.post_ts as char(16)) as Post,
  RSP.post_audit_id
from 
  dhsdb2.wvs_rsp RSP
  join dhsdb2.indv_name_srch INS on RSP.indv_id = INS.indv_id
  join dhsdb2.indv_demg_srch IDS on RSP.indv_id = IDS.indv_id
where 
  RSP.indv_id in 
    (select 
       SRSP.indv_id 
     from 
       dhsdb2.wvs_rsp SRSP 
     where 
       DRSP.indv_id = RSP.indv_id and
       SRSP.rsp_ver > 1.0)
  and rsp.clse_ts is null 
order by
  RSP.rsp_ver;

Upvotes: 0

Related Questions