Reputation: 797
Not able to group the selected data
I am a learner of database, i guess i am asking very basic question, please bear with me.
I wanted to fetch a list of values from a table. List of values will be ordered according to some criteria.
Example: I have a column named courseOffered in college table, where i am saving a string like: java, c++, android etc.
i have another two columns called primarySubject and secondarySubject in user table.
My first fetching logic is, according to primarySubject, row values of college table must be first priority in the list, then according to secondarySubject, row values of college table must be second priority in the list and rest values will be at last appended, which are unrelated to primarySubject or secondarySubject.
What so far i tried:
SELECT *
FROM test.college
WHERE Courses_Offered LIKE CONCAT('%',RTRIM((SELECT
Primary_Subject
FROM test.user
WHERE Email = '[email protected]')),'%')
OR Courses_Offered LIKE CONCAT('%',RTRIM((SELECT
Secondary_Subject
FROM test.user
WHERE Email = '[email protected]')),'%')
OR LOWER(Location)IN(SELECT
LOWER(Location)
FROM test.user
WHERE Email = '[email protected]')
the above query fetches all the data but not as i wanted above, its fetching as rows are saved in table, now i want a kind of grouping around every fetch in the above query, how should i do it?
Upvotes: 0
Views: 37
Answers (1)
Reputation: 19882
Like this you can use group by and order by
SELECT *
FROM test.college
WHERE Courses_Offered LIKE CONCAT('%',RTRIM((SELECT Primary_Subject FROM test.user WHERE Email = '[email protected]')),'%')
OR Courses_Offered LIKE CONCAT('%',RTRIM((SELECT Secondary_Subject FROM test.user WHERE Email = '[email protected]')),'%')
OR LOWER(Location)IN(SELECT LOWER(Location) FROM test.user WHERE Email = '[email protected]')
GROUP by college.column_name
ORDER BY college.first_column_name ASC , college.second_column_name DESC
Upvotes: 1