[Resolved] Union, groupby and count..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Union, groupby and count..

Author

Topic

aberbotimue
Starting Member

26 Posts

Posted - 2009-02-25 : 16:34:02

Hi all.. I have had this up on another dev forum, but its sounding like its not a streight forward bit of SQL, as I thought, and think upgrading to a SQL specific forum was in order...

I spent hours on it, but to be honest, thats not a great thing, as my sql sucks!! but needs must!!

so..

I want to take a search term from a web site, and do a search on a table using those words.. I hope to eventualy use the full text searching facility, but untill I have that in place, I am looking for a quick sql version, that does most of the job for me..

I had got this far, and it was working, but not putting the results in a usefull order..


SELECT     *
FROM         items
WHERE     (item LIKE '%nokia%')
union
SELECT     *
FROM         items where
(item LIKE '%original%')

allowing a results that have both words, in any order..

My next thinking was, make it a union ALL to give me dupes, and then count the dupes, and order by a count on the dupes, so that the item,s that have more than one of the words in, will gravitate to the top of the list??

Does that make sense!!

so i played around somthing like this ( i know this isn't it, but its one of the many permutations, that have just numbed my brain now! ) but shows where i was trying to go with this one..


SELECT     *, count (itemid)
FROM         items
WHERE     (item LIKE '%nokia%')
group by itemid
union ALL
SELECT     *
FROM         items where
(item LIKE '%original%')
group by itemid

the moment I put the count (itemid) in there, it winged about group by - and then when I added that, all hell broke loose..

I still want the items that only have one of the search terms , but the ones with two nearer the top, hence ordering by the number of duplicates, and doing specific searhes on each word..

i.e.

a search for "original nokia" would yeild

the original bing bang nokia
nokia original buttons
nokia phone
original hits from abba

any advice would be greatfull..

Nathan
(aberbotimue)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 16:37:39

[code]SELECT Item,
COUNT(*) AS Hits
FROM (
SELECT Item
FROM Items
WHERE Item LIKE '%nokia%'

UNION ALL

SELECT Item
FROM Items
WHERE Item LIKE '%original%'
) AS d
GROUP BY Item[/code]

E 12°55'05.63"
N 56°04'39.26"

aberbotimue
Starting Member

26 Posts

Posted - 2009-02-25 : 16:50:19

You had that solved, faster than I could get my sql open to test it!!

I bow to your skill, and speed, and thank you so much.. This really had me stumped..

Thank you..

N
(A)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources