CODEWITHSUNDEEP
mysqlsql
rix
rix

Reputation: 10632

SQL query, limit to 1 result per specif column

Given the query below, I can get a result set containing all new stories from the location id's listed in the query and sorted by date. What I don't understand is how to rewrite the query so that it is limited to 1 result (the latest) from each location ID.

Is it possible to do this, or should I just process the result set afterwards?

Thanks in advance,

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM 
{node} node
INNER JOIN {field_data_field_story_location} field_data_field_story_location 
ON node.nid = field_data_field_story_location.entity_id 
AND (field_data_field_story_location.entity_type = 'node'
WHERE (( (node.status = '1') AND (node.type IN  ('_news_story')) 
AND (field_data_field_story_location.field_story_location_tid IN  
('38', '44', '46', '45', '30', '36', '37', '29', '33', '34', '28', '56', '21', '49', '32')) ))
ORDER BY node_created DESC

Upvotes: 0

Views: 118

Answers (1)

Ari
Ari

Reputation: 157

You could process the result set afterwards, but it's not efficient in case of big amount of data.

To be more efficient you could write n queries (one for each field_story_location_tid) with limit 1 and then join the result set using the UNION sintax

Upvotes: 2

Related Questions