retrieving a sql results with a specific Id using Rank and Partition

[gottimukkala]

Hi I have a stored procedure that returns all the docs uploaded ordered by creation date. Now i want to change it like show a document with the id "1234" as always on top and the remaining results as today after to that document. Does anyone have any one idea. Any Help please......! thanks#

[Mark Graham]

What springs to mind.... The first thing I would look at is returning two result sets (presumably your first would have one row in it and your second would have many, excluding the unique row you've already selected, ordered by create date) from your store proc. Then using a ADO.Net DataReader I would merge the two result sets in code. Here's some pseudo with some c#

// create return object (a list) here

using ( SqlDataReader reader = command.ExecuteReader( ) )
{
    // read first doc

    if ( reader.Read( ) )
    {
        // add data to a DTO, or business object then add to results list
    }

    // move to next result set and read the other docs

    **reader.NextResult();**

    while ( reader.Read( ) )
    {
        // add data to a DTO, or business object then add to results list
    }
}
// return list - the merged results

This is still only one stored proc call; one trip across the wire (not that two trips in this case would make the slightest bit of difference). Has this helped?

Mark Graham (MCP) // The Doodler blogging about C#, Asp.Net, and Design Patterns at: csCoffee[^] and contributing at dotNet Notepad[^]