Post reply

Query question

  • February 2, 2012 at 1:22 pm

    #254740

    Hello All,

    I need help to figure out this query.

    From the sample data,

    If a program has form (cb_req and c_req) Then I have to get record with c_req form

    else I have to get cb_req form. (There are other forms aswell but the condition applies to only mentioned forms)

    SO for the code I have posted, I want only two records

    1. CDmaril with c_req form

    2. TRharris with cb_req form

    CREATE TABLE #pt

    (

    program varchar(10),

    pstatus varchar (6),

    form varchar (12)

    )

    insert into #pt

    select 'CDmaril', null, 'cb_req'

    union all

    select 'CDmaril', 'uc', 'c_req'

    union all

    select 'TRharris', null, 'cb_req'

    select * from #pt

  • February 2, 2012 at 1:45 pm

    #1442065

    Nice job posting ddl and sample data.

    Here is one way off the top of my head.

    select program, pstatus, form

    from

    (

    select *

    , ROW_NUMBER() over (partition by program order by case when form = 'c_req' then 0 else 99 end) as RowNum

    from #pt

    group by program, pstatus, form

    ) x where RowNum = 1

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 2, 2012 at 1:48 pm

    #1442067

    One approach is to assign an order to the different form values. Using your sample data the following seems to work

    ;

    with orderedpt as

    (

    select program,

    pstatus,

    form,

    row_number() over (partition by program order by form asc) as rn

    from #pt

    where form in ('cb_req','c_req')

    )

    select program

    ,pstatus

    ,form

    from orderedpt where rn = 1;

  • February 2, 2012 at 1:59 pm

    #1442073

    Sean,

    Is the group by clause needed in this case?

    I tend to use CTEs (and probably overuse them). Is there any advantage to your approach (I think it is called an inline view in some DBMSs)?

    Dave

  • February 2, 2012 at 2:06 pm

    #1442075

    Dave Brooking (2/2/2012)

    Sean,

    Is the group by clause needed in this case?

    I tend to use CTEs (and probably overuse them). Is there any advantage to your approach (I think it is called an inline view in some DBMSs)?

    Dave

    Dave you are absolutely correct that the group by is not needed. I was going down a different path using a group by at first and left it in there. 😉

    I don't think there is any distinct advantage of subquery as opposed to a cte in this case. They both do pretty much the same thing. The only major difference is I used a case to build a sort order instead of sorting the string. No real advantage there either unless you have strings that don't sort the way you want them to.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 2, 2012 at 2:07 pm

    #1442076

    Thank you Sean!

    I have tried something like this:

    select program, pstatus, form from

    (select * ,

    ROW_NUMBER() over (partition by program order by len(form)) as RowNum

    from #pt

    group by program, pstatus, form) x where RowNum = 1

    Not sure, If it causes any issues?

    Thanks again!

  • February 2, 2012 at 2:27 pm

    #1442092

    ssc_san (2/2/2012)

    Thank you Sean!

    I have tried something like this:

    select program, pstatus, form from

    (select * ,

    ROW_NUMBER() over (partition by program order by len(form)) as RowNum

    from #pt

    group by program, pstatus, form) x where RowNum = 1

    Not sure, If it causes any issues?

    Thanks again!

    That would work too but I would be a little worried about order by len(form). Instead of len you should use datalength. If the datatype is char it won't work. In your original description you said a certain value should have precedence over another. That is why I coded mine the way i did. With the datalength approach you will get incorrect data if at some point the important string is no longer the longest value.

    Here is a quick example of what I mean about len vs datalength. You have to be careful with it. 😀

    declare @CharVal char(10) = 'a'

    declare @VarCharVal varchar(10) = 'a'

    select LEN(@CharVal), LEN(@VarCharVal), DATALENGTH(@CharVal), DATALENGTH(@VarCharVal)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 2, 2012 at 2:45 pm

    #1442100

    Sean,

    Your CASE statement is a more tailorable solution, rather than relying on the string sort order. I can see the requirements changing and the string sort needing to be changed into a CASE statement. If I were the OP I would use your CASE statement over the reliance on a LENGTH check or string sort, as the intention of the ordering is explicitly defined (consider c_req before cb_req). I had to double check whether _ or b took precedence in a string sort.

    You said you thought there was no distinct advantage between the two approaches (CTE v subquery) in this case. Can you think of anywhere that using a subquery would be a better solution over a CTE?

    I know each situation should be treated separately and appropriate solutions need to be analysed to determine which performs best, but I have got into a habit of using CTEs and have neglected to check the performance of a CTE solution versus a subquery solution. Do I need to revisit my queries?

    I will test both versions going forward.

    Dave

  • February 2, 2012 at 2:59 pm

    #1442105

    Got it Sean, Thanks!

    Thanks Dave!

  • February 5, 2012 at 1:31 pm

    #1442846

    ssc_san (2/2/2012)

    Got it Sean, Thanks!

    Thanks Dave!

    Would you post your final solution, please? It may help others in the future. Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 5, 2012 at 2:35 pm

    #1442856

    Hi Jeff,

    I am using it to populate list of values for my report. And it is not going to change in the future (As per User), So I took Sean's approach.

    Thanks!

  • February 5, 2012 at 10:51 pm

    #1442894

    ssc_san (2/5/2012)

    Hi Jeff,

    I am using it to populate list of values for my report. And it is not going to change in the future (As per User), So I took Sean's approach.

    Thanks!

    Got it. Thanks for the feedback on this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply