Query help for results order
-
I'm a programmer forced into being a DBA fo a project.
My query is something like:
SELECT * FROM Products WHERE Product_ID IN (31, 5, 7, 9, 12)
I would like to have the database do as much work as possible for me and return the results in the order I have specified (31, 5, etc)
I haven't tried it, but I'm guessing my simple query will return the records whatever order it wants to.
So how do I specify the order?
-
What about having a second table (ProductSort) that stores your specific sort order...
Product_ID SortOrder
31 1
5 2
7 3
9 4
12 5
Then do your SELECT statement like this...
SELECT * FROM Products P INNER JOIN ProductSort PS ON P.Product_ID = PS.Product_ID ORDER BY PS.SortOrder
-
Here's a fun one :
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
CREATE PROCEDURE dbo.GetListedProducts @Ids as varchar(8000)
As
SET NOCOUNT ON
SELECT ProductId, name FROM dbo.Products P inner join
(Select EachID, Rank from dbo.fnSplit_Set ('31,5,7,9,12', ',')) dtIDS on P.ProductId = dtIDS.EachID
/*might need a cast on dtIDS.EachID, check the execution plan to be sure you don't get a clustered index scan on the products table*/
order by dtIDS.Rank
SET NOCOUN OFF
GO
-
This would be:
SELECT
case Product_ID
when 31 then 1
when 5 then 2
when 7 then 3
when 9 then 4
when 12 then 5
end,
*
FROM Products
WHERE Product_ID IN (31, 5, 7, 9, 12)
order by 1
-
How do you make this dynamic so that the code still works in a sp?
-
Remi, can you walk me through your solution?
-
SELECT *
FROM Products WHERE ProductID IN (31, 5, 7, 9, 12)
ORDER BY CHARINDEX(', '+CAST(ProductID as varchar)+',',','+'31, 5, 7, 9, 12'+',')
or if using variable/parameter
DECLARE @ID varchar(100)
SET @ID = '31,5,7,9,12'
SELECT *
FROM Products
WHERE CHARINDEX(','+CAST(ProductID as varchar)+',',','+@ID+',') > 0
ORDER BY CHARINDEX(','+CAST(ProductID as varchar)+',',','+@ID+',')
Far away is close at hand in the images of elsewhere.
Anon. -
Now that's what I was talking about in the other post
.Arrays and Lists in SQL Server - Slow
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Declare @vcDelimiter varchar(3)
Declare @IDs varchar(8000)
SET @IDs = '31,5,7,9,12'
SET @vcDelimiter = ','
Select * from dbo.SysObjects O inner join (
Select dtSplitted.EachID, Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0) dtIds on O.id = dtIds.EachID
order by dtIds.Rank
(3 ligne(s) affectée(s))
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY
[Expr1003] ASC))|--Compute Scalar(DEFINE
[Expr1002]=substring([@vcDelimiter]+[@IDs]+[@vcDelimiter], [N].[PkNumber]+len([@vcDelimiter]), charindex([@vcDelimiter], [@vcDelimiter]+[@IDs]+[@vcDelimiter], [N].[PkNumber]+len([@vcDelimiter]))-[N].[PkNumber]-len([@vcDelimiter])), [Expr1003]=len(substring([@IDs], 1, [N].[PkNumber]))-len(replace(substring([@IDs], 1, [N].[PkNumber]), ',', ''))+1))|--Nested Loops(Inner Join, OUTER REFERENCES
[N].[PkNumber]))|--Filter(WHERE
substring([@vcDelimiter]+[@IDs]+[@vcDelimiter], [N].[PkNumber], len([@vcDelimiter]))=[@vcDelimiter] AND len(substring([@vcDelimiter]+[@IDs]+[@vcDelimiter], [N].[PkNumber]+len([@vcDelimiter]), charindex([@vcDelimiter], [@vcDelimiter]+[@IDs]+[@vcDelimiter], [N].[PkNumber]+len([@vcDelimiter]))-[N].[PkNumber]-len([@vcDelimiter])))>0))| |--Clustered Index Seek(OBJECT
[Documentation].[dbo].[Numbers].[Pk_Number] AS [N]), SEEK[N].[PkNumber] < len([@vcDelimiter]+[@IDs]+[@vcDelimiter])) ORDERED FORWARD)|--Compute Scalar(DEFINE
[O].[schema_ver]=[O].[base_schema_ver]&~15, [O].[stats_schema_ver]=[O].[base_schema_ver]&15, [O].[type]=Convert(If ([O].[xtype]='UQ' OR [O].[xtype]='PK') then Convert('K') else Convert([O].[xtype])), [O].[userstat]=Convert(If ([O].[xtype]='S' OR [O].[xtype]='U') then 1 else 0), [O].[sysstat]=Convert(If ([O].[xtype]='S') then 1 else If ([O].[xtype]='V') then 2 else If ([O].[xtype]='U') then 3 else If ([O].[xtype]='P') then 4 else If ([O].[xtype]='RF') then 4 else If ([O].[xtype]='X') then 4 else If ([O].[xtype]='L') then 5 else If ([O].[xtype]='D') then 6 else If ([O].[xtype]='R') then 7 else If ([O].[xtype]='TR') then 8 else If ([O].[xtype]='PK') then 9 else If ([O].[xtype]='UQ') then 9 else If ([O].[xtype]='C') then 10 else If ([O].[xtype]='F') then 11 else If ([O].[xtype]='AP') then 13 else 0+If ([O].[xtype]='S' OR [O].[xtype]='U') then (If (([O].[status]&1)Convert(0)) then 16 else 0+If (([O].[status]&2)Convert(0)) then 32 else 0+64+If (substring([O].[name], 1, 1)=Convert('#')) then 256 else 0+If (([O].[status]&Convert(0x01000000))Convert(0)) then 512 else 0+If (([O].[status]&Convert(0x200000))Convert(0)) then 1024 else 0+If (([O].[status]&Convert(0x04000000))Convert(0)) then 2048 else 0+If (([O].[status]&4)Convert(0)) then 8192 else 0+If (substring([O].[name], 1, 2)=Convert('##')) then -32768 else 0) else 0), [O].[indexdel]=Convert(([O].[base_schema_ver]&~15)/65536), [O].[refdate]=Convert([O].[crdate]), [O].[version]=Convert(0), [O].[deltrig]=Convert(If ([O].[xtype]='S' OR [O].[xtype]='U') then objectproperty([O].[id], Convert('TableDeleteTrigger')) else If ([O].[xtype]='TR') then [O].[parent_obj] else 0), [O].[instrig]=Convert(If ([O].[xtype]='S' OR [O].[xtype]='U') then objectproperty([O].[id], Convert('TableInsertTrigger')) else 0), [O].[updtrig]=Convert(If ([O].[xtype]='S' OR [O].[xtype]='U') then objectproperty([O].[id], Convert('TableUpdateTrigger')) else 0), [O].[seltrig]=Convert(0), [O].[category]=Con|--Clustered Index Seek(OBJECT
[Documentation].[dbo].[sysobjects].[sysobjects] AS [O]), SEEK[O].[id]=Convert(substring([@vcDelimiter]+[@IDs]+[@vcDelimiter], [N].[PkNumber]+len([@vcDelimiter]), charindex([@vcDelimiter], [@vcDelimiter]+[@IDs]+[@vcDelimiter], [N].[PkNumber]+len([@vcDelimiter]))-[N].[PkNumber]-len([@vcDelimiter])))) ORDERED FORWARD)(7 ligne(s) affectée(s))
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @ID varchar(100)
SET @ID = '31,5,7,9,12'
SELECT *
FROM dbo.Sysobjects
WHERE CHARINDEX(','+CAST(id as varchar)+',',','+@ID+',') > 0
ORDER BY CHARINDEX(','+CAST(id as varchar)+',',','+@ID+',')
(2 ligne(s) affectée(s))
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY
[Expr1002] ASC))|--Compute Scalar(DEFINE
[Expr1002]=charindex(','+Convert([sysobjects].[id])+',', ','+[@ID]+',', NULL)))|--Compute Scalar(DEFINE
[sysobjects].[cache]=Convert(0), [sysobjects].[category]=Convert(If (([sysobjects].[status]&Convert(0x80000000))Convert(0)) then 2 else 0+If (([sysobjects].[replinfo]&1)Convert(0)) then 32 else 0+If (([sysobjects].[replinfo]&2)Convert(0)) then 64 else 0+If (([sysobjects].[replinfo]&4)Convert(0)) then 256 else 0+If ([sysobjects].[xtype]='P' AND ([sysobjects].[status]&2)Convert(0)) then 16 else 0+If ([sysobjects].[xtype]='D' AND [sysobjects].[parent_obj]Convert(0)) then 2048 else 0+If ([sysobjects].[xtype]='S' OR [sysobjects].[xtype]='U') then (If (([sysobjects].[status]&Convert(0x20))Convert(0)) then 1 else 0+If (([sysobjects].[status]&Convert(0x0400))Convert(0)) then 4 else 0+If (([sysobjects].[status]&Convert(0x0800))Convert(0)) then 8 else 0+If (([sysobjects].[status]&Convert(0x1000))Convert(0)) then 128 else 0+If (([sysobjects].[status]&Convert(0x0100))Convert(0)) then 512 else 0+If (([sysobjects].[status]&Convert(0x0200))Convert(0)) then 1024 else 0+If (([sysobjects].[status]&Convert(0x2000))Convert(0)) then 2048 else 0+If (([sysobjects].[status]&Convert(0x4000))Convert(0)) then 4096 else 0+If (([sysobjects].[status]&Convert(0x10))Convert(0)) then 16384 else 0) else 0), [sysobjects].[seltrig]=Convert(0), [sysobjects].[updtrig]=Convert(If ([sysobjects].[xtype]='S' OR [sysobjects].[xtype]='U') then objectproperty([sysobjects].[id], Convert('TableUpdateTrigger')) else 0), [sysobjects].[instrig]=Convert(If ([sysobjects].[xtype]='S' OR [sysobjects].[xtype]='U') then objectproperty([sysobjects].[id], Convert('TableInsertTrigger')) else 0), [sysobjects].[deltrig]=Convert(If ([sysobjects].[xtype]='S' OR [sysobjects].[xtype]='U') then objectproperty([sysobjects].[id], Convert('TableDeleteTrigger')) else If ([sysobjects].[xtype]='TR') then [sysobjects].[parent_obj] else 0), [sysobjects].[version]=Convert(0), [sysobjects].[refdate]=Convert([sysobjects].[crdate]), [sysobjects].[indexdel]=Convert(([sysobject|--Clustered Index Scan(OBJECT
[Documentation].[dbo].[sysobjects].[sysobjects]), WHEREcharindex(','+Convert([sysobjects].[id])+',', ','+[@ID]+',', NULL)>0))(4 ligne(s) affectée(s))
Long story short : my way = 37% and yours = 63%.
-
/*this creates a sequence table*/
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
--this creates a function to split the list values
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
--this would be the proc to get the products
CREATE PROCEDURE dbo.GetListedProducts @Ids as varchar(8000)
As
SET NOCOUNT ON
SELECT ProductId, name FROM dbo.Products P inner join
(Select EachID, Rank from dbo.fnSplit_Set (@Ids, ',')) dtIDS on P.ProductId = dtIDS.EachID
/*might need a cast on dtIDS.EachID, check the execution plan to be sure you don't get a clustered index scan on the products table*/
order by dtIDS.Rank
SET NOCOUN OFF
--test the proc
Exec dbo.GetListedProducts '31,5,7,9,12'
What part are you having difficulties with?
-
I'm confused about splitting the list values. Why is this done?
-
Nevermind, I see what it is for.
-
Generally, this is something that will change between each call from the app
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply