July 31, 2008 at 1:57 am
HI,
I have a requirment where i need to fetch the data in the same order as the id passed to it.
here is the problem i am facing
i pass the ID =1,2,3 to my query as following
SELECT Col1
FROM table1
WHERE col_ID IN (1,2,3)
it fetch me data in order of ID's =1,2,3
Now i pass the in reverse order i.e. ID =3,2,1 to my query as following
SELECT Col1
FROM table1
WHERE col_ID IN (3,2,1)
it fetch me data in same order of ID's =1,2,3
Now in this case i want that the ouput should be in order 3,2,1
i.e output order should be same as order of input IDs.
Could somebody help me in this......
Thanxs in Adv.
July 31, 2008 at 2:22 am
To get the data in a specific order, you will have to specify an order by clause. There is no other way to guarantee an order.
Do you just want to sort the data ascending or descending by Col_ID, or is there more to this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2008 at 3:01 am
hi Gail,
Thaxs for your prompt reply.
Acutaly order by will give me the result in ACS or DESC order. But in my case i need result in the order .... of Id's passed to the query.
eg.
i pass the ID =1,2,3 to my query as following
SELECT Col1
FROM table1
WHERE col_ID IN (1,2,3)
Output Result order would be 1,2,3
Now if i pass the ID =2,1,3 to my query
then Output Result order would be 2,1,3
I hope i m making my point clear here .......
i.e in wht ever order i pass the id to query it should result the data in same order.....
July 31, 2008 at 3:39 am
How are you passing in your ID's?
Here's a possibility:
SELECT a.Col1
FROM table1 a
INNER JOIN (
SELECT 1 AS RowID, 2 AS col_ID UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 3 UNION ALL
) b ON b.col_ID = a.col_ID
ORDER BY b.RowID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 4:33 am
Hi Chris,
thanxs for the solution.But this would only be applicable if Id's r fixed not for dynamically fetched ID's.
Actualy I have a Stored Procedure which fetch certain data based on these Id. These Id's r random and there order and number is dependent on users selection.Hence it could be any id
eg. first time it may be 232,33,546,2,4,5,7,8,1091,223,3434
and next time it may be 33,54,6,88,2113,42,54,77
waiting for reply.:)
July 31, 2008 at 4:49 am
Can I ask why you want the results ordered like this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2008 at 4:50 am
There's an answer in this, Meeraj:
SELECT * FROM [dbo].[uftSplitString] ('232,33,546,2,4,5,7,8,1091,223,3434', ',')
CREATE FUNCTION [dbo].[uftSplitString]
(
@String VARCHAR(8000),
@Delimiter VARCHAR(255)
)
RETURNS
@Results TABLE
(
SeqNo INT IDENTITY(1, 1),
Item VARCHAR(8000)
)
AS
BEGIN
INSERT INTO @Results (Item)
SELECT SUBSTRING(@String+@Delimiter, number,
CHARINDEX(@Delimiter, @String+@Delimiter, number) - number)
FROM Numbers
WHERE number <= LEN(REPLACE(@String,' ','|'))
AND SUBSTRING(@Delimiter + @String,
number,
LEN(REPLACE(@Delimiter,' ','|'))) = @Delimiter
ORDER BY number RETURN
END
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 5:07 am
hi Chris,
I used your function but it gives me error on execution Invalid object name 'Numbers'.. Also could you please explain a bit about it.
Thanxs in Adv.
July 31, 2008 at 5:21 am
Please try this function. this is very commonly used function in SQL Server.
SELECT * FROM [dbo].[fnSplit] ('232,33,546,2,4,5,7,8,1091,223,3434', ',')
CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
July 31, 2008 at 6:39 am
Hi Hemantjemmy,
Thanxs for your function. i already hav this function.
Well as i did not found any direct way to get my problem solved hence a did some work around.I feel this could be helpful when you want to display data in same order as your input ids. This logic has over head of creating temp tables .Bt it is always good to have something rather then nothing.
Here is the work around.
--Temp Table created to hold final data.
DECLARE @Temp_Company Table
(
ID[int]IDENTITY(1,1) NOT NULL,
Company[varchar](MAX) NULL
)
--Temp Table created to hold splited commaseprated data.
DECLARE @Temp_Ids Table
(
RowId int identity(1,1),
Ids[varchar](MAX) NULL
)
DECLARE @MaxRowID int
DECLARE @LoopIndex int
--Initialize loop index
SELECT @LoopIndex = 1
-- Using the Function fnSplit same as given by Hemantjemmy
--Set the count
SELECT @MaxRowID = Count(*) FROM fnSplit ('132,133,546,222,334,565,753,156', ',')
--Insert in Temp Table
INSERT INTO @Temp_Ids (Ids) SELECT * FROM fnSplit ('132,133,546,222,334,565,753,156', ',')
WHILE @LoopIndex <= @MaxRowID
BEGIN
INSERT INTO @Temp_Company
(
Company
)
Select companyName from EmpCompany
where Empid = (select Ids FROM @Temp_Ids
WHERE RowId = @LoopIndex)
SET @LoopIndex = @LoopIndex + 1
END
select * from @Temp_ReportViewField
If any better ways ...plz. let me know
Thanxs.:)
August 1, 2008 at 2:54 am
Hi Meeraj, apologies for the delay in replying to you.
Here's a script for creating a Numbers or Tally table, which you will see often on this forum.
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Numbers
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000
IDENTITY(int,1,1) AS number
INTO dbo.Numbers
FROM master.dbo.syscolumns sc1,
master.dbo.syscolumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_numbers_number PRIMARY KEY CLUSTERED (number)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Numbers TO PUBLIC
If you run the script to create the table, then the function uftSplitString will work. It returns a table variable with two columns, the first is an id column, the second is one of the values entered in the string.
Here's some sample data and results to show you how it works:
DROP TABLE #Table1
CREATE TABLE #Table1 (Col1 INT, col_ID INT)
INSERT INTO #Table1 (Col1, col_ID)
SELECT 10,2 UNION ALL
SELECT 20,4 UNION ALL
SELECT 30,5 UNION ALL
SELECT 40,7 UNION ALL
SELECT 50,8 UNION ALL
SELECT 60,33 UNION ALL
SELECT 70,223 UNION ALL
SELECT 80,232 UNION ALL
SELECT 90,546 UNION ALL
SELECT 100,1091 UNION ALL
SELECT 110,3434 UNION ALL
SELECT 110,1 UNION ALL
SELECT 110,3 UNION ALL
SELECT 110,6 UNION ALL
SELECT 110,3438 UNION ALL
SELECT 110,3439
SELECT a.*, b.*
FROM #Table1 a
INNER JOIN (
SELECT SeqNo, Item FROM [dbo].[uftSplitString] ('232,33,546,2,4,5,7,8,1091,223,3434', ',')
) b ON b.Item = a.col_ID
ORDER BY b.SeqNo
Results:
Col1 col_ID SeqNo Item
----------- ----------- ----------- ----------
80 232 1 232
60 33 2 33
90 546 3 546
10 2 4 2
20 4 5 4
30 5 6 5
40 7 7 7
50 8 8 8
100 1091 9 1091
70 223 10 223
110 3434 11 3434
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 6:03 am
Thanxs.
August 1, 2008 at 9:55 pm
Chris Morris (8/1/2008)
Hi Meeraj, apologies for the delay in replying to you.Here's a script for creating a Numbers or Tally table, which you will see often on this forum.
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE dbo.Numbers
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000
IDENTITY(int,1,1) AS number
INTO dbo.Numbers
FROM master.dbo.syscolumns sc1,
master.dbo.syscolumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_numbers_number PRIMARY KEY CLUSTERED (number)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Numbers TO PUBLIC
If you run the script to create the table, then the function uftSplitString will work. It returns a table variable with two columns, the first is an id column, the second is one of the values entered in the string.
Here's some sample data and results to show you how it works:
DROP TABLE #Table1CREATE TABLE #Table1 (Col1 INT, col_ID INT)
INSERT INTO #Table1 (Col1, col_ID)
SELECT 10,2 UNION ALL
SELECT 20,4 UNION ALL
SELECT 30,5 UNION ALL
SELECT 40,7 UNION ALL
SELECT 50,8 UNION ALL
SELECT 60,33 UNION ALL
SELECT 70,223 UNION ALL
SELECT 80,232 UNION ALL
SELECT 90,546 UNION ALL
SELECT 100,1091 UNION ALL
SELECT 110,3434 UNION ALL
SELECT 110,1 UNION ALL
SELECT 110,3 UNION ALL
SELECT 110,6 UNION ALL
SELECT 110,3438 UNION ALL
SELECT 110,3439
SELECT a.*, b.*
FROM #Table1 a
INNER JOIN (
SELECT SeqNo, Item FROM [dbo].[uftSplitString] ('232,33,546,2,4,5,7,8,1091,223,3434', ',')
) b ON b.Item = a.col_ID
ORDER BY b.SeqNo
Results:
Col1 col_ID SeqNo Item
----------- ----------- ----------- ----------
80 232 1 232
60 33 2 33
90 546 3 546
10 2 4 2
20 4 5 4
30 5 6 5
40 7 7 7
50 8 8 8
100 1091 9 1091
70 223 10 223
110 3434 11 3434
Nicely done, Chris!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2008 at 2:29 am
Hi Jeff
You pick up a chunk of code off this forum and use it for a year or two; who's credited with it becomes lost in the mists of time. I believe both the tally table script and the UDF are your own work. Many thanks Jeff, both have been incredibly useful.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 4, 2008 at 5:21 am
Thanks, Chris... :blush: (Didn't expect that)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply