You must Sign In to post a response.
  • Category: SQL Server

    How to get sequential records from database using IN statement?

    I have below query which getting records with mismatched order by using IN statement.

    By execution of below query i got following type of result,

    "SQL">SELECT patient_name , AGE , patient_gender , pd.mrd_number , Item_Desc , item_id , pd.pID, Pending_Qty
    FROM vw_DSInfo_prescription_master pm
    INNER JOIN vw_DSInfo_prescription_details pd
    ON pm.mrd_number = pd.mrd_number
    WHERE pm.pID = pd.pre_ID
    AND pd.pre_ID IN ('1964920','1965969','1964583','1965139','1965959')
    AND pm.mrd_number = 12345
    AND pd.Pending_Qty NOT like '0%'
    AND Pending_qty NOT LIKE '-%';

    By execution of below query i got following type of result,

    1. 1st 4 rows of pre_Id 1964583
    2. 2nd 2 rows of pre_Id 1964920
    3. 3rd 3 rows of pre_Id 1965969
    4. 4th 5 rows of pre_Id 1965139
    5. 5th 7 rows of pre_Id 1965959

    this order is mismatched and sorted by datetime but i define this order (1964920.1965969,1964583,1965139,1965959) in 'IN' statement and i want all rows by defined order only.
  • #769135
    Hi

    can you share your table data also then you can order by in your primary key or records created date also

    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #769138
    If you want your own order you have to create a temp table and create a sequence to order the result according to your wish.
    Note: change the datatype of pre_ID according to the datatype of vw_DSInfo_prescription_details

    Find the query below.

    CREATE TABLE #Temp1 (seqid integer identity(1,1),pre_ID integer );
    insert into #Temp1 values(1964920)
    insert into #Temp1 values(1965969)
    insert into #Temp1 values(1964583)
    insert into #Temp1 values(1965139)
    insert into #Temp1 values(1965959)

    SELECT patient_name , AGE , patient_gender , pd.mrd_number , Item_Desc , item_id , pd.pID, Pending_Qty
    FROM vw_DSInfo_prescription_master pm
    INNER JOIN vw_DSInfo_prescription_details pd
    ON pm.mrd_number = pd.mrd_number
    JOIN #Temp1 t on t.pre_ID= pd.pre_ID
    WHERE pm.pID = pd.pre_ID
    --AND pd.pre_ID IN ('1964920','1965969','1964583','1965139','1965959')
    AND pm.mrd_number = 12345
    AND pd.Pending_Qty NOT like '0%'
    AND Pending_qty NOT LIKE '-%';
    order by t.seqid

  • #769142
    Hi

    what is your logic for order in your Data?

    can you share with me

    when i order your data output this

    3 1964583
    1 1964920
    4 1965139
    5 1965959
    2 1965969

    can you tell me what criteria based order your data?

    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #769159
    Hi Priya,

    Using IN operator you can't expect the data in same order which you pass inside IN operator, as you requested in question you need in one type of "ORDER" in the name itself we have a solution, i.e. you have to use ORDER BY Clause to get your order either in ascending or descending.

    Ex:

    select * from tablename
    where your conditions
    ORDER BY col1 ASC


    Hope this is helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments