SQL help

[lampstax]

I have a table (story_history) that stores all revisions for a story.   Each story can have multiple revisions (versions), each stored as a row in the table.

 

Here's the table structure:

 

| story_historyID  | int(11)                      | NO   | PRI | NULL| auto_increment |
| storyID          | int(11)                      | NO   |     | 0              |
| public           | tinyint(4)                   | NO   | MUL | 0              |
| copy_ready       | tinyint(4)                   | NO   |     | 0              |
| copy_complete    | tinyint(4)                   | NO   |     | 0              |
| sectionID        | int(11)                      | NO   | MUL | 0              |
| is_blog          | tinyint(4)                   | NO   |     | 0              |
| title            | varchar(255)                 | YES  | MUL | NULL              |
| headline         | varchar(255)                 | YES  |     | NULL              |
| body             | text                         | NO   | MUL | NULL              |
| body_more        | text                         | NO   |     | NULL              |
| bio              | text                         | NO   | MUL | NULL              |
| sourceID         | int(11) unsigned             | NO   | MUL | 0              |
| teaser           | text                         | NO   |     | NULL              |
| date_start       | datetime                     | NO   | MUL | 0000-00-00 00:00:00 |                |
| date_modified    | datetime                     | NO   |     | 0000-00-00 00:00:00 |                |
| type             | enum('story','blog','wire')  | YES  | MUL | story              |
| mediaID          | int(11)                      | NO   |     | 0              |
| imageID          | int(11)                      | YES  |     | NULL              |
| teaser_imageID   | int(11)                      | NO   |     | 0              |
| notes            | mediumtext                   | NO   |     | NULL              |
| notes_editor     | mediumtext                   | NO   |     | NULL              |
| comments         | enum('Off','On','Read Only') | NO   |     | Off              |
| modified_by      | varchar(24)                  | NO   |     |              |
| story_author     | varchar(100)                 | NO   |     |              |
| coverage_stories | varchar(100)                 | NO   |     |              |
| related_links    | varchar(100)                 | NO   |     |              |
| related_stories  | varchar(100)                 | NO   |     |

 

I am trying to write an SQL to find the historyID of ONLY the first version of each story.  Gotta make sure other versions doesn't show in result.  Does that make sense ?

 

Been scratching my head for the past 30 minutes, but coming up blank.   Hope you guys can help.

 

Thanks

[lampstax]

I actually came up with

 

SELECT distinct(storyID) FROM story_history ORDER BY story_historyID DESC 

 

but in hindsight, I think this only would give me a random version of each story and sort the random returned version in chronological order, not necessarily return the first version of each story.

 

[php_dave]

SELECT MIN(story_historyID) from story_history Group By StoryID

 

Not tested.. but I think the above would work -  will select the smallest HistoryID for each storyID.. assuming the smallest historyID is the earliest version?

 

[Barand]

I was think along the same lines, but I'd add storyID to the selection so you know to which story each history belongs.

SELECT StoryID, MIN(story_historyID) from story_history Group By StoryID

 

If the assumption is wrong then we're going to need some date checking

 

SELECT a.StoryID, a.story_historyID
FROM story_history a
INNER JOIN (SELECT StoryID, MIN(date_start) as date_start FROM story_history GROUP BY StoryID) as b
USING (StoryID, date_start)

[lampstax]

Thats perfect.  I love you guys.  I wonder when my SQL skills will be as l337.

 

[lampstax]

When a user creates the first version, they become the creating assigned editor of said story.  A row is recorded in story_history with modified_by holding their user ID when they first create the story (ie version 1)

 

I tried to modify it so that when you enter in a user's name (ie 'frel') it will find all the stories where the matching user(s) is the assigned editor and return the most recent version of the story.

 

My query though, is timing out.  =T

 

SELECT 		s.* 
FROM		story 				AS s 
		JOIN story_history 	AS h 	ON h.storyID = s.storyID
            JOIN usr			AS u	ON u.usrID = h.modified_by
WHERE 		h.story_historyID IN ( SELECT MIN(story_historyID) as story_historyID FROM story_history GROUP BY storyID )
		AND concat(u.first_name,' ',u.last_name) LIKE '%frel%'                       
GROUP BY 	h.storyID  

[fenway]

It's possible you've created a dependent subquery... can we see the EXPLAIN?