I'm using SQL Server 2012

I have a simple table that has a Nullable (int) Position and a (datetime) Created field.

I would like to do a select * from this table order by created desc but ...if a record has a Position value NOT NULL, we should insert that record into that specific position on the result set ...

Looking at the SQlFiddle ...the expected result of my query should be: "one", "two", "three", "four". "five", "six"

Note: record "four" has position value 9 and that means that if the result set would be returning 15 records, it will be on position 9.

In the case of two records with same position (ie. 9) the one with older date takes precedence.

  • 1
    Quite complex requirements. What is not clear is what should happen in the case of two rows with same position (ie. 9). The one with the oldest date ok, takes precedence and goes to position 9. Where does the other go? To position 10? What happens if there is another row (or more!) with position 10? – ypercubeᵀᴹ Dec 17 '14 at 1:45
  • the other one appends to the one before.... 9 takes precedence on 10 (always) so it will be 9,9,10. Good points on those. – SF Developer Dec 17 '14 at 19:09

I don't think that this completely satisfies your request, but it is a start. If you want further ordering, I think you need to go into more detail with the sorting rules.

Also, the example data from your fiddle has "five" with a created date older than "four".


select * 
  from table1 
  order by coalesce(position,2147483647)
    , created;

Query Results:

id          position    name                 created
----------- ----------- -------------------- -----------------------
1           1           one                  2014-10-10 00:00:00.000
6           2           two                  2014-12-12 00:00:00.000
3           9           five                 2014-08-08 00:00:00.000
5           9           four                 2014-11-11 00:00:00.000
2           NULL        three                2014-09-09 00:00:00.000
4           NULL        six                  2014-12-31 00:00:00.000
  • It's a good start yes. Ideally, record with Position value 9 should be the 9th record but since here we only have 6 records, it should be the last two so 5th and 6th. Right now we have a sort gives value to the fact that we have a value ...but that's not our ultimate goal. – SF Developer Dec 17 '14 at 0:20
  • Ideally the sort should be by DATE DESC ...but if there is a record that has an actual expected position, that record should be inserted and forced into that result set position (disregarding the created date). – SF Developer Dec 17 '14 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.