select all rows except top row [duplicate]

Question

how do I return all rows from a table except the first row. Here is my sql statement:

Select Top(@TopWhat) * 
from tbl_SongsPlayed 
where Station = @Station 
order by DateTimePlayed DESC

How do I alter my SQL statement to return all rows except the first row.

Many thanks

Teri

did you google?i just copied the title and search and found so many answers! here is one : #6027625 – Reduplication

what version of sql server? – Troop

Answer 1

SQL 2012 also has the rather handy OFFSET clause:

Select Top(@TopWhat) *
from tbl_SongsPlayed 
where Station = @Station 
order by DateTimePlayed DESC
OFFSET 1 ROWS

Pincushion

Answer 2

Depending on your database product, you can use row_number():

select *
from
(
  Select s.*,
    row_number() over(order by DateTimePlayed DESC) rn
  from tbl_SongsPlayed s
  where s.Station = @Station 
) src
where rn >1

Troop

Answer 3

The EXCEPT operator (http://msdn.microsoft.com/en-us/library/ms188055.aspx)

Select Top(@TopWhat) *
from tbl_SongsPlayed 
Except  Select Top(1) *
from tbl_SongsPlayed 
where Station = @Station 
order by DateTimePlayed DESC

'Not In' was another clause that can be used.

Confucius

The first subquery is neither filtered nor ordered. The result of the complete query may not be what the OP is after. – Varicocele

Answer 4

Assuming you have a unique ID for tbl_SongsPlayed, you could do something like this:

// Filter the songs first
With SongsForStation
As   (
   Select *
   From   tbl_SongsPlayed
   Where  Station = @Station
)
// Get the songs
Select *
From   SongsForStation
Where  SongPlayId <> (
   // Get the top song, most recently played, so you can exclude it.
   Select Top 1 SongPlayId
   From   SongsForStation
   Order By DateTimePlayed Desc
   )
// Sort the rest of the songs.
Order By
   DateTimePlayed desc
        Where 

Seroka