Category: SQL Server

31 Mar 2017 11:08

Joined:	25/04/2016
Level:	Bronze
Points:	13

Points:	5

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

31 Mar 2017 11:58

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	2

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

31 Mar 2017 13:06

Joined:	16/08/2007
Level:	Silver
Points:	248

Points:	4

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

31 Mar 2017 17:02

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	2

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

05 Apr 2017 13:26

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

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/

How to get sequential records from database using IN statement?

About dotnetspider.com

Quick Links

General

Services