November 23, 2010 at 4:07 am
I Select some data with:
SELECT *
FROM custom.Routing
WHERE @var LIKE Number + '%'
Field Number contains for example: 999 and 99912
When I search for @var = 99912 I get two lines: 999 and 99912
I want an order in my result:
99912, 5 matches
999, 3 matches
matches == count of matching digits
November 23, 2010 at 4:55 am
Something like this ?
Be warned though , this is non-sargable ,and will perform badly over a large dataset.
SELECT number,count(*)
FROM custom.Routing
WHERE @var LIKE Number + '%'
group by number
order by count(*) desc
November 23, 2010 at 5:05 am
no, this counts the rows...
I need the information about the biggest hit rate.
When I search for 99911 both rows matches, 999 and 99911. But the hit rate of 99911 is higher (5)...
November 23, 2010 at 6:25 am
wagner-670519 (11/23/2010)
no, this counts the rows...I need the information about the biggest hit rate.
When I search for 99911 both rows matches, 999 and 99911. But the hit rate of 99911 is higher (5)...
wagner-670519 (11/23/2010)
matches == count of matching digits
So, what's the difference between matches and hit rate?
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
November 23, 2010 at 10:05 am
Maybe:
ORDER BY LEN( Number ) DESC
Scott Pletcher, SQL Server MVP 2008-2010
November 26, 2010 at 1:39 am
Sorry about my bad explanation. I try to specify it.
I have a table which contains the first digits of telephone numbers (column Number)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'custom') BEGIN
EXEC (N'CREATE SCHEMA custom')
END
GO
-------------------------------------------------------------------------------
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'custom' AND TABLE_NAME = 'Routing' ) BEGIN
CREATE TABLE custom.Routing (
[GUID]uniqueidentifier NOT NULL,
ClientsGUIDuniqueidentifier NOT NULL,
GroupsGUIDuniqueidentifier NOT NULL,
Numbervarchar(32) NOT NULL,
CONSTRAINTPK_Routing PRIMARY KEY CLUSTERED([GUID])
/* you do not have this tables,
CONSTRAINTFK_Routing_Clients FOREIGN KEY(ClientsGUID) REFERENCES common.Clients ([GUID]),
CONSTRAINTFK_Routing_Groups FOREIGN KEY(GroupsGUID) REFERENCES common.Groups ([GUID]) ON DELETE CASCADE
*/
)
END
GO
Let's put some data in it....
INSERT INTO custom.Routing ([GUID],ClientsGUID,GroupsGUID,Number) VALUES (NEWID(),'00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000001','0180')
INSERT INTO custom.Routing ([GUID],ClientsGUID,GroupsGUID,Number) VALUES (NEWID(),'00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000002','01')
INSERT INTO custom.Routing ([GUID],ClientsGUID,GroupsGUID,Number) VALUES (NEWID(),'00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000003','02')
The result is this:
GUID ClientsGUID GroupsGUID Number
------------------------------------ ------------------------------------ ------------------------------------ --------------------------------
04F9310E-3D9B-4A3E-A437-7FA37BAF54A9 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000003 02
D374CCE2-92B1-4FDA-B9E3-950249A7A2C7 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 01
5A7939DA-CEB6-473A-BA7C-B29F5E8B0F82 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 0180
(3 Zeile(n) betroffen)
Now I put a stored procedure in it (here a simplified version)
CREATE PROCEDURE custom.Routing_getGroupsGUIDbyNumber
@caller NVARCHAR(32)
AS
SELECT GroupsGUID FROM custom.Routing WHERE @caller LIKE Number + '%'
RETURN
GO
Now I fire my query:
EXEC custom.Routing_getGroupsGUIDbyNumber '0180'
The result is
GroupsGUID
------------------------------------
00000000-0000-0000-0000-000000000002
00000000-0000-0000-0000-000000000001
(2 Zeile(n) betroffen)
because
@caller LIKE Number + '%'
0180 LIKE 0180% AND 0180 LIKE 01%
My question is: I need an order for this result, like this:
GroupsGUID matchedDigits
------------------------------------ ------------------------------------
00000000-0000-0000-0000-000000000002 2
00000000-0000-0000-0000-000000000001 4
(2 Zeile(n) betroffen)
because 01 are 2 digits and 0180 are 4 digits...
greetz...
November 26, 2010 at 6:23 am
Okay im sure im totally missing something here but adding either
ORDER BY Convert(integer, number)
or
ORDER BY Len(Number)
or
ORDER BY Number
gives me the result that your after.
Add
Len(Number) matchedDigits
to the select and its exactly the same result as your example.
Maybe your example SP is to simplified? And its really something else thats causing problems?
/T
November 26, 2010 at 7:11 am
oh my god. sure you all are right. I think it it was too easy to solve it by myself.
there are some bugs in my head...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply