Union and order by query not working

[thedevilinu]

Here is a code which will select the people in decreasing order of relevance.

 

SELECT * FROM table WHERE city='Jaipur' and subjects LIKE '%All Subjects%'

UNION

SELECT * FROM table WHERE city='Jaipur' and (subjects LIKE '%All%' or qualifications LIKE '%All%' )

UNION

SELECT * FROM talbe WHERE city='Jaipur' and (subjects LIKE '%Subjects%' or qualifications LIKE '%Subjects%' )

 

and this works good. The problem starts when I try to sort them by ID.

 

So when I try to do this..

 

SELECT * FROM table WHERE city='Jaipur' and subjects LIKE '%All Subjects%'

UNION

SELECT * FROM table WHERE city='Jaipur' and (subjects LIKE '%All%' or qualifications LIKE '%All%' )

UNION

SELECT * FROM talbe WHERE city='Jaipur' and (subjects LIKE '%Subjects%' or qualifications LIKE '%Subjects%' )

ORDER BY id DESC

............this works fine as well but it messes up the order.. all the elemtns from 3 queries get mixed up.

 

what I want is something like this.. but this doesn't work..I hope what I want is fairly clear from the query but it's not working in practice. Please help.

SELECT * FROM table WHERE city='Jaipur' and subjects LIKE '%All Subjects%' ORDER BY id DESC

UNION

SELECT * FROM table WHERE city='Jaipur' and (subjects LIKE '%All%' or qualifications LIKE '%All%' ) ORDER BY id DESC

UNION

SELECT * FROM talbe WHERE city='Jaipur' and (subjects LIKE '%Subjects%' or qualifications LIKE '%Subjects%' ) ORDER BY id DESC

 

[gizmola]

We don't know what id is, so I'm going to just take a guess that what you mean is that you want to retain the groupings of the 3 seperate queries, even though you are combining them into one result with the union.

 

SELECT *, 1 as grouporder FROM table WHERE city='Jaipur' and subjects LIKE '%All Subjects%' 
UNION
SELECT *, 2 as grouporder  FROM table WHERE city='Jaipur' and (subjects LIKE '%All%' or qualifications LIKE '%All%' )
UNION
SELECT *, 3 as grouporder  FROM talbe WHERE city='Jaipur' and (subjects LIKE '%Subjects%' or qualifications LIKE '%Subjects%' )
ORDER BY grouporder, id DESC

[thedevilinu]

Wow.. nice code.. it is small and works just the way I wanted it to. I did manage to do the same thing by making an array of queries.. it was way too much php..

 

I am also trying to now sort the tutors by the number of times the keywords repeat in a profile.. it would be just a modification of the last code..

 

so if a user inputs subject as 'social science', the code should sort people (ids) by

the number of times

1. 'Social science' repeats in various fields (subjects, qualifications, experience)

2. 'social' repeats in various fields (subjects, qualifications, experience)

3. 'science' repeats in various fields (subjects, qualifications, experience)

 

whichever has the highest count will get the top priority.

[thedevilinu]

I just faced a problem with the last suggestion. What's happening now is that the same results are being repeated whereas unique results came earlier. Please advise when i wasnt doing the grouporder thing

[gizmola]

The basics of getting a grouped result like that is to do a GROUP BY tutor_id or whatever is the column that indicates the tutor.  You can then include the summary count(*). 

 

SELECT tutor_id, COUNT(*) AS countof WHERE .... GROUP BY tutor_id ORDER BY countof DESC

[thedevilinu]

The only problem I am facing with the group by method is that it repeats the same tutors again and again when used with UNION. Is there any way to counter this. I really appreciate the help

[gizmola]

Let's see the query you currently are using.

[thedevilinu]

So now my query looks like this...

SELECT id, COUNT(*) AS countof FROM table WHERE city='delhi'  and subjects LIKE '%math%' 
UNION 
SELECT id, COUNT(*) AS countof FROM table WHERE state='Delhi'  and subjects LIKE '%math%' 
UNION 
SELECT id, COUNT(*) AS countof FROM table WHERE city='delhi'  and (subjects LIKE '%math%' or qualifications LIKE '%math%' ) 
UNION 
SELECT id, COUNT(*) AS countof FROM table WHERE state='Delhi'  and (subjects LIKE '%math%' or qualifications LIKE '%math%' ) 
Group by id ORDER BY countof DESC 

and gives the following error..

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[gizmola]

No, that will not work what you want is something like this:

 

SELECT id, count(*) as countof FROM (
SELECT id FROM table WHERE city='delhi'  and subjects LIKE '%math%' 
UNION ALL
SELECT id FROM table WHERE state='Delhi'  and subjects LIKE '%math%' 
UNION ALL
SELECT id FROM table WHERE city='delhi'  and (subjects LIKE '%math%' or qualifications LIKE '%math%' ) 
UNION ALL
SELECT id FROM table WHERE state='Delhi'  and (subjects LIKE '%math%' or qualifications LIKE '%math%' ) ) as allusers
Group by id ORDER BY countof DESC

[thedevilinu]

problem here .. two things are happening..

 

first of all, it's not ordering them in the order of increasing order but rather than that, it's ordering them in the asc id ..

and secondly.. now none of the other content but id is displayed.

However, the rows aren't repeating now so that's a good part.

 

wht's the significance of allusers in the code?

[gizmola]

allusers is just an alias for the union'ed result

 

I don't really know what you mean by the ordering. 

 

A group by requires the elimination of duplicate rows, and in this case you're trying to get users and a count of the number of times they come up as a match for the criteria.  If you want to then have access to individual information about a user, you would need to inner join this result to the user table by user_id.

 

You have never provided information about the actual table structures, so these are all educated guesses.  This seems to be made a lot more difficult than it needs to be, as it is unclear why you are doing a union of 4 seperate tables in the first place, when a query of a single table would work if the design was typical.

[thedevilinu]

Ahh. Sorry for not making it clear at the very first place..

I am not counting the number of times a particular user appears in the four queries.

What I wanted to do was to arrange users by the keywords they have in their profile and number of times these keywords appear in their profile

 

so for a query like this..

SELECT *, 1 as grouporder FROM table WHERE city='Jaipur' and subjects LIKE '%social science%'

UNION

SELECT *, 2 as grouporder  FROM table WHERE city='Jaipur' and (subjects LIKE '%social%' or qualifications LIKE '%social%' )

UNION

SELECT *, 3 as grouporder  FROM talbe WHERE city='Jaipur' and (subjects LIKE '%Science%' or qualifications LIKE '%Sceince%' )

ORDER BY grouporder, id DESC

 

if someone is looking for a tutor which teaches "social science".. I will first of all find tutors which have keyword "social science" and then find tutors which have either "social " or "science" in their profile.. and whoever has these keywords repeated highest number of times.. gets prominance..

Please let me know if I have made myself clear enough before writing another code.

[gizmola]

I feel like this thread is going in circles.  You started with a question on mechanics which I answered.  I then showed you some other techniques which are useful in certain circumstances.  At this point the problem you were trying to solve in the original question is now the thing that you don't like. 

 

Now that it's clear what you are really trying to do is derive some sort of "relevance" score to differentiate people in a list, I can tell you with certainty that you're on the wrong track.

 

One other thing I feel needs to be added at this point, is that when you do a query like this:

 

SELECT * FROM table WHERE subjects LIKE '%something%'

 

That query can not use an index.  So as an example, if your "table" has 10k rows that match city='Jaipur', every single one of those rows will have to be examined (scanned) to determine whether or not a match exists. 

 

I don't know if this is a solution or not for your application, but mysql does offer a fulltext search which does compute an internal relevance metric that can be used to order results. 

 

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match

 

As an alternative many people use sphinx  http://sphinxsearch.com/about/sphinx/

 

Based on what you're actually trying to do, either of those are more likely to get you what you're trying to do, and also will provide some scalability and performance, whereas %word% queries simply can not.

[thedevilinu]

Thanks for the valuable suggestion and all the time you spent on the problem.

Can you please tell me if fulltext sesarch work on phpmyadmin as well as it said it works on Isam only.

[gizmola]

You're confused.  MySQL has pluggable "engines" which allow it to support different storage systems, which can have vastly different mechanics and capabilities.  The default engine is "myisam".  Fulltext indexes require that the table where you've created the fulltext index be a myisam engine table.

 

phpMyAdmin is a web application that provides an administrative interface to mysql.  It is not an engine.

 

Often people use the innodb engine for a number of reasons.  MySQL fully supports the use of multiple engines at the same time, so you can have a database where you have an innodb engine table and a myisam engine table in it. 

 

You can look at the table schema in phpMyAdmin and see what engine is being used, but my guess is that unless you knew specifically what you were doing, your tables are already myisam, so you will not have an issue adding a fulltext index.

[thedevilinu]

Thankyou!! Thankyou!! Thankyou!! I don't have any money with me now else would have definitely made a donation. You are really good. Thanks again.

[gizmola]

No worries, and good luck with your project.