How to query 1st occurrence and move to nxt group in each set of group

[sam4help]

Dears,

myTable

ID Name Details
1 XYZ CAT BAT RAT MAT
1 XYZ ANY MANY
2 MNO TEN PEN HEN
3 ABC ONE TWO THREE FOUR
3 ABC MATTER CATTER CHATTER
4 XYZ FAT CAT CHAT
5 XYZ AN PAN CAN
6 NNN CAT ANT PAT

If I search with 'like' on column 'DETAILS'; I need to return rows as below

Search Criteria Need Output ID WHERE DETAILS LIKE '%CAT%' PRESENT AND NAME=XYZ
Results Needed:

ID
___
1
4

I have query that gives correct results but it takes more time because the table data is getting bigger so it seems checks each rows which is not required it needs to check only first occurrence of the requested word and check the next ID

SELECT     DISTINCT ID FROM         myTable WHERE  Name='XYZ' AND  (DETAILS LIKE '%CAT%')

 

[sam4help]

Thanks Got it after few tries.

Just had to use TOP 1 instead of DISTINCT thats it

SELECT     TOP 1 ID FROM         myTable WHERE  Name='XYZ' AND  (DETAILS LIKE '%CAT%')

Thanks,

Sam

 

[sam4help]

I was little over enthusiastic on this
still need to work on this its not giving desired results

 

[gmmastros]

What does your actual table data look like? Specifically...

How many rows are in this table?
How many unique "Name" are there?
How long does it take for your query (as is) to execute?

The reason I ask is.... if there are only (on average) a small handful of rows for each name, and you have an index on the name column, this query should be lightening quick.

Can you run the following queries in a query window and post the output here?

Select Count(*) As TotalRowCount,
       Count(Distinct Name) As NameCount
From   myTable

sp_helpindex 'myTable'

The first query should give me a pretty good idea about the selectivity of the name column. The more selective it is, the more an index will help. The second query will tell me if there is already an appropriate index.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Dear George; thanks for reply

How many rows are in this table?
How many unique "Name" are there?

Its ever increasing table, it stores OCR Data page wise (table name is trOCR);

So, we have two tables one is the Main Record Table with doc id and other details and other OCR data of that doc id attached image store in this trOCR.
This OCR data is stored page wise in each records so if we have 700 pages then we will have 700 records for that doc id.

Now I have created a non clustered index as below so its much much better results now

index_name	index_description	index_keys
INDX_OCR	nonclustered located on PRIMARY	trID

 

[gmmastros]

so its much much better results now

I'm glad to hear that. Is the performance acceptable?

Given the nature of what you are trying to accomplish, I would encourage you to investigate "Full Text Search". I suggest you read this:

http://technet.microsoft.com/en-us/library/ms142547.aspx

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

yea thanks Geogre, as of now its good enough don't know much how it will work as the data grows.

As far as "Full Text Search" is concerned, seems it works only on MS SQL 2008 and in our case few of the users use 2005 also. So, I feel this is the only option left.

 

[sam4help]

the index created is as below:
on table
table name: trOCR
fields: trID, trOCR, trPageNo

/*index for ocr data*/
CREATE NONCLUSTERED INDEX [INDX_OCR]
ON [dbo].[trOCR] ([trID])
INCLUDE ([trOCR])
GO

 

[gmmastros]

Full text search does exist in SQL2005. If this is not something you want to pursue right now, just remember that it is an option later if you run in to more performance problems.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

ok, George thanks. Sure will keep that in mind.