May 24, 2012 at 2:44 am
Hi,
i have a huge database where users can search according to their requirements(this is location base system).my search query is working well.
Now i want to show users related search...here is my queryselect TOP 2 COMPANY,CategoryName,comp_kws,ID,
len(COMPANY) - len(replace(COMPANY, @qry, '')) as cntcmp ,
len(CategoryName) - len(replace(CategoryName, @qry, ''))
as cntcat,
len(comp_kws) - len(replace(comp_kws, @qry, ''))
as cntkey--,
--len(locStr) - len(replace(locStr, @qry, ''))
--as cntStr
from myView Where locStr is not null and COMPANY is not null and CategoryName is not null and comp_kws is not null GROUP BY ID,COMPANY,CategoryName,comp_kws--,locStr
order by cntcmp DESC,cntcat DESC,cntkey DESC--,cntStr DESC
the issue with is query is this shows similar records...i dnt want that either company be same OR company and CategoryName be same....same fields should be different and also two fields must not have same data/record.How it can be?
May 24, 2012 at 4:16 am
Not very clear what the problem is. You have duplicate records which you don't want to show?
Use : SELECT DISTINCT.
Otherwise, please provide DDL and sample data as per link at the bottom of my signature.
May 24, 2012 at 5:05 am
If i pass search parameter like 'pizza',i am getting result like this which i do not want
COMPANY CategoryName comp_kws .......................
PIZZA HUT PIZZA PIZZA ........................
PIZZA HUT PIZZA PIZZA .........................
If you see i have duplicate records in All FIELD and also most seriously Duplicate records between two or more columns....
DISTINCT is not working in my case,here is my code
select DISTINCT COMPANY,CategoryName,comp_kws,ID,
len(COMPANY) - len(replace(COMPANY, 'pizza', '')) as cntcmp ,
len(CategoryName) - len(replace(CategoryName, 'pizza', ''))
as cntcat,
len(comp_kws) - len(replace(comp_kws, 'pizza', ''))
as cntkey
from vwSearchResult Where locStr is not null and COMPANY is not null and CategoryName is not null and comp_kws is not null GROUP BY ID,COMPANY,CategoryName,comp_kws--,locStr
order by cntcmp DESC,cntcat DESC,cntkey DESC
May 24, 2012 at 5:30 am
I can only suggest you to follow this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 24, 2012 at 5:51 am
If you see i have duplicate records in All FIELD and also most seriously Duplicate records between two or more columns....
DISTINCT is not working in my case,here is my code
1) Cannot see this.
2) What does 'ALL FIELD' mean? Why capitalise it? It is not a defined term, AFAIK.
3) "is not working" is not very specific. You've been here long enough to know that you need to provide more detail before people can understand what you are talking about.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 24, 2012 at 6:00 am
Ok here is my detail...If you please run this code,it will give you duplicate data in same rows as well as in different row....i do not want any record to be exist in same field or in any other field
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
COMPANY NVARCHAR(50),
CAT NVARCHAR(50),
KW NVARCHAR(50)
)
SET IDENTITY_INSERT #mytable1 ON
INSERT INTO #mytable1
(ID, COMPANY, CAT,KW)
SELECT '4','PIZZA HUT SINGAPORE PTDLTD','PIZZA','PIZZA' UNION ALL
SELECT '37','PIZZA HUT SINGAPORE PTE LTD','PIZZA','PIZZA' UNION ALL
SELECT '44','PIZZA HUT','PIZZA','PIZZA' UNION ALL
SELECT '54','PIZZA HUT','PIZZA ','PIZZA' UNION ALL
SELECT '55','PIZZA HUT UAE','PIZZA PHP','PIZZA' UNION ALL
SELECT '81','PIZZA HUT PK','PIZZA INDO','PIZZA' UNION ALL
SELECT '86','PIZZA HUT AD','PIZZA ABC','PIZZA' UNION ALL
SELECT '96','PIZZA HUT','PIZZA' ,'PIZZAggg'
SET IDENTITY_INSERT #mytable1 OFF
select COMPANY,CAT,KW,
len(COMPANY) - len(replace(COMPANY, 'pizza', '')) as cntcmp ,
len(CAT) - len(replace(CAT, 'pizza', ''))
as cntcat,
len(KW) - len(replace(KW, 'pizza', ''))
as cntkw
from #mytable1 GROUP BY COMPANY,CAT,KW
order by cntcmp DESC,cntcat DESC,cntkw desc
Hope this formating you people will like and will apreciate me
May 24, 2012 at 6:16 am
Right now your query returns:
COMPANYCATKWcntcmpcntcatcntkw
PIZZA HUTPIZZAPIZZA555
PIZZA HUTPIZZAPIZZAggg555
PIZZA HUT ADPIZZA ABCPIZZA555
PIZZA HUT PKPIZZA INDOPIZZA555
PIZZA HUT SINGAPORE PTDLTDPIZZAPIZZA555
PIZZA HUT SINGAPORE PTE LTDPIZZAPIZZA555
PIZZA HUT UAEPIZZA PHPPIZZA555
Could you please show what exactly do you want instead.
May 24, 2012 at 6:29 am
PIZZA HUTPIZZAPIZZA555
PIZZA HUTPIZZAPIZZAggg555
PIZZA HUT ADPIZZA ABCPIZZA555
PIZZA HUT PKPIZZA INDOPIZZA555
PIZZA HUT SINGAPORE PTDLTDPIZZAPIZZA555
PIZZA HUT SINGAPORE PTE LTDPIZZAPIZZA555
PIZZA HUT UAEPIZZA PHPPIZZA555
If you see i have bolded and underlined same data(mean duplicated result)....i do not want this result......
i need only 2 records which are DISTINCT in same field and with other field....for example a record in COMPANY should not exist in CAT or other...mean PIZZA and PIZZA HUT only exist once in this result...Hope you got it!!!
May 24, 2012 at 7:03 am
Methew (5/24/2012)
PIZZA HUTPIZZAPIZZA555
PIZZA HUTPIZZAPIZZAggg555
PIZZA HUT ADPIZZA ABCPIZZA555
PIZZA HUT PKPIZZA INDOPIZZA555
PIZZA HUT SINGAPORE PTDLTDPIZZAPIZZA555
PIZZA HUT SINGAPORE PTE LTDPIZZAPIZZA555
PIZZA HUT UAEPIZZA PHPPIZZA555
If you see i have bolded and underlined same data(mean duplicated result)....i do not want this result......
i need only 2 records which are DISTINCT in same field and with other field....for example a record in COMPANY should not exist in CAT or other...mean PIZZA and PIZZA HUT only exist once in this result...Hope you got it!!!
I do understand the results you get it's not what you want. That's why I've asked you to show exact results as you want to see them. Please don't try to explain again, it does not help dur to the way you are using SQL terminology: "eg. record in COMPANY should not exist in CAT "
If you could show the expected result based on your sample data, it would be the best way to explain what you really want to achieve.
May 24, 2012 at 7:14 am
i need like this
PIZZA HUT PIZZA PIZZAggg
PIZZA HUT AD PIZZA ABC
PIZZA HUT PK PIZZA INDO
May 24, 2012 at 7:35 am
Why there is no records for
PIZZA HUT SINGAPORE PTDLTD
PIZZA HUT SINGAPORE PTE LTD
PIZZA HUT UAE
?
What makes them different to PIZZA HUT AD and PIZZA HUT PK?
also I cannot see PIZZA PHP in CAT. What makes this value different to PIZZA ABC and PIZZA INDO?
If the answer to both of the above is "nothing", then the following should return what you want (sorry, but I cannot even image why would you need this sort of crap, looks like you somehow misread the requirements...)
SELECT dco.COMPANY, dca.CAT, dk.KW,
len(COMPANY) - len(replace(COMPANY, 'pizza', '')) as cntcmp,
len(CAT) - len(replace(CAT, 'pizza', ''))as cntcat,
len(KW) - len(replace(KW, 'pizza', '')) as cntkw
FROM
(SELECT COMPANY, ROW_NUMBER() OVER (ORDER BY COMPANY) RN FROM (SELECT DISTINCT COMPANY FROM #mytable1) co) dco
FULL JOIN
(SELECT CAT, ROW_NUMBER() OVER (ORDER BY CAT) RN FROM (SELECT DISTINCT CAT FROM #mytable1) ca) dca
ON dca.RN =dco.RN
FULL JOIN
(SELECT KW, ROW_NUMBER() OVER (ORDER BY KW) RN FROM (SELECT DISTINCT KW FROM #mytable1) k) dk
ON dk.RN =ISNULL(dco.RN,dca.RN)
May 24, 2012 at 7:52 am
Let me clear you first.i want to show related search.For example if you search for PIZZA i will show you PIZZA near to your location.This is search.Now related search is,i want to show only Six records(due to space on page and load.also sorry that i show 7 instead of 6 in above post) related to your search term PIZZA.
I leaved Other records because i got first 6 records and Also i do not want Duplication.
I would like that all fields(COMPANY,CAT,KW) have 2,2 each.
like
PIZZA HUT PIZZA PIZZAggg
PIZZA HUT AD PIZZA ABC here any value from this field if it has
(in current case third colomn KW do not have any distinct value...)
May 24, 2012 at 9:00 am
Methew (5/24/2012)
Let me clear you first.i want to show related search.For example if you search for PIZZA i will show you PIZZA near to your location.This is search.Now related search is,i want to show only Six records(due to space on page and load.also sorry that i show 7 instead of 6 in above post) related to your search term PIZZA.I leaved Other records because i got first 6 records and Also i do not want Duplication.
I would like that all fields(COMPANY,CAT,KW) have 2,2 each.
like
PIZZA HUT PIZZA PIZZAggg
PIZZA HUT AD PIZZA ABC here any value from this field if it has
(in current case third colomn KW do not have any distinct value...)
Sorry mate, I cannot understand any of your sentences.
What 7 records instead of 6? Your setup for #mytable1 conatins 8 records, your original query returns 7 (as there is one clear duplicate 'PIZZA HUT','PIZZA ','PIZZA'). In expected results you did show that you want 3 records back.
My query returns no duplicate records. You don't want value duplication across all fields?
And what "all fields(COMPANY,CAT,KW) have 2,2 each." does mean?
Try:
SELECT dco.COMPANY, dca.CAT, dk.KW,
len(COMPANY) - len(replace(COMPANY, 'pizza', '')) as cntcmp,
len(CAT) - len(replace(CAT, 'pizza', ''))as cntcat,
len(KW) - len(replace(KW, 'pizza', '')) as cntkw
FROM
(SELECT COMPANY, ROW_NUMBER() OVER (ORDER BY COMPANY) RN FROM (SELECT DISTINCT TOP 2 COMPANY FROM #mytable1) co) dco
FULL JOIN
(SELECT CAT, ROW_NUMBER() OVER (ORDER BY CAT) RN FROM (SELECT DISTINCT TOP 2 CAT
FROM #mytable1
WHERE CAT NOT IN (SELECT DISTINCT TOP 2 COMPANY FROM #mytable1)
) ca) dca
ON dca.RN =dco.RN
FULL JOIN
(SELECT KW, ROW_NUMBER() OVER (ORDER BY KW) RN FROM (SELECT DISTINCT TOP 2 KW
FROM #mytable1
WHERE KW NOT IN (SELECT DISTINCT TOP 2 COMPANY FROM #mytable1)
AND KW NOT IN (SELECT DISTINCT TOP 2 CAT
FROM #mytable1
WHERE CAT NOT IN (SELECT DISTINCT TOP 2 COMPANY
FROM #mytable1)
)
) k) dk
ON dk.RN =ISNULL(dco.RN,dca.RN)
*looks like crap to me
May 24, 2012 at 9:43 am
Thanks alot...you solved my problem.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply