2

my first post...

I have a news story table where users flag stories, in priority 1,2,3,4 for displaying on the website home page.

The problem is that as they add stories the old ones get pushed down onto page two, three etc, in the CMS. They then flag a new "1", "2" etc, without clearing the old 1s, 2s etc - which remain in the data, just in case the new number 1, gets "demoted" (the flag removed) or deleted.

What I want to get out is the newsid and headline, of most recent of each number "1", "2", "3" and "4".

I have found some similar posts, but its not the timestamp that's important its the flag field (called "xorder"). The relevant fields in the "newsStories" table are below:

newsid      int
headline    varchar(60)
xorder      int (number or null)
xtimestamp  datetime - this is the publish date

In this scenario the xtimestamp field is important as its the date/time they want the news story to appear (the publish date) rather than a timestamp field, which would be updated by MySQL. We don't want to show stories where the published date/time is greater than the current system timestamp (allows stories to be embargoed) We generate the query when the web page loads, so our criteria reads something like:

where xorder IS NOT NULL
AND xorder > 0
AND xtimestamp <= 201604271800 (for 6pm today)

(This is slightly simplified as there are other criteria).

I am sorting by xtimestamp desc and xorder asc, and just getting the first 4 records found, but this is not giving them in the correct xorder.

Hope this makes sense, any help appreciated.

Jon_the_eye
  • 39
  • 4
  • so you want to order xorder from 1 - 4? just add ORDER BY xorder ASC – Matt Apr 27 '16 at 16:30
  • Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Shadow Apr 27 '16 at 16:46
  • order by xorder ASC gets me all the 1s, then all the 2s. I want the most recent 1, then the most recent 2, then the most recent 3 then the most recent 4.By "most recent", I mean the xtimestamp field. – Jon_the_eye May 03 '16 at 11:24

2 Answers2

0

take a look at the keywords order by and limit in the select chapter in your SQL documentation

Stian Skjelstad
  • 2,277
  • 1
  • 9
  • 19
0

The answer is to UNION ALL the statements. For me, this was 4 separate select statements each with a limit 1 and union all between them. It does work, but it seems a bit clunky.

Jon_the_eye
  • 39
  • 4