CODEWITHSUNDEEP
sql-server-2005sorting
Xaver
Xaver

Reputation: 1001

Data sorting in query

I work with SQL Server 2005. I have a table in which FK: two columns (book number, bookshelf number). A PK = FK + Datetime column. And also I have the column of value. I want to create query, which sort the data as follows.

I have this data set:

BookNumber   BookshelfNumber          DateTime          Value
    0              0             12.01.2012 14:40:42      4
    0              0             12.01.2012 14:45:42      5
    0              0             12.01.2012 14:48:42      9
    0              1             12.01.2012 14:38:42      1
    0              1             12.01.2012 14:42:42      4
    0              1             12.01.2012 14:48:42      1
    1              1             12.01.2012 14:38:42      5
    1              1             12.01.2012 14:48:42      7
    1              1             12.01.2012 14:58:42      4

After query I want this result:

BookNumber   BookshelfNumber          DateTime          Value
    0              0             12.01.2012 14:40:42      4
    0              1             12.01.2012 14:38:42      1
    1              1             12.01.2012 14:38:42      5

Upvotes: 1

Views: 107

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

You can use the ROW_NUMBER function to add a number to your dataset that

  • restarts numbering by each change in Booknumber & BookshelfNumber (PARTITION BY)
  • Numbers the result from oldest to newest datetime (ORDER BY)

SQL Statement

SELECT  *
FROM    (
          SELECT  BookNumber
                  , BookShelfNumber
                  , DateTime
                  , Value
                  , rn = ROW_NUMBER() OVER (PARITION BY BookNumber, BookshelNumber ORDER BY DateTime)
          FROM    Books
        ) b
WHERE   rn = 1

Upvotes: 5

Related Questions