CODEWITHSUNDEEP
mysql
Bluemagica
Bluemagica

Reputation: 5158

How to write this mysql query

Table Structure

id | term | result
==================
1  | t1   | 0
2  | t2   | 0
3  | t1   | 34
4  | t1   | 23
5  | t2   | 10
6  | t2   | 10
7  | t3   | 20

The table is for a search table where term is the keyword someone was searching for, and result is the number of results returned at that time. Now from this table I need to retrieve 1) number of times a term was searched for and 2) the number of results that were returned when it was last search for.

for the first one I can do count(term) group by term, and for 2 probably ORDER by id DESC, but I don't know how to do both in a single query. Any help?

Upvotes: 0

Views: 112

Answers (4)

Joseph Keeler
Joseph Keeler

Reputation: 31

You can use the GROUP_CONCAT() function to aggregate the result fields, and then use SUBSTRING_INDEX to get back the one you want.

SELECT COUNT(term), SUBSTRING_INDEX(GROUP_CONCAT(result ORDER BY id DESC), ',', 1) FROM table GROUP BY term

Upvotes: 1

Grisha Weintraub
Grisha Weintraub

Reputation: 7996

   select term, count(*), (select result from my_table where id = max(t.id)) 
   from my_table t 
   group by term

See SQL Fiddle

Upvotes: 1

Fathah Rehman P
Fathah Rehman P

Reputation: 8761

Use following query 

      select id,term,count(*) as 'count',result from (select * from table33 
 order by id desc) as abc group by term

Upvotes: -1

eggyal
eggyal

Reputation: 126025

You want the groupwise maximum:

SELECT term, count, result
FROM   my_table NATURAL JOIN (
         SELECT term, COUNT(*) count, MAX(id) id FROM my_table GROUP BY term
       ) t

See it on sqlfiddle.

Upvotes: 4

Related Questions