Geek Answers Handbook

Select all rows except the top row

how to return all rows from a table except the first row. Here is my sql query:

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

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

Thank you very much

+4
source share
4 answers

SQL 2012 also has a pretty convenient OFFSET clause:

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

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
+5
source

Assuming you have a unique identifier for tbl_SongsPlayed , you can 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
0
source

already "Chrisb" gave a very neat answer. But you can also try this ...

EXCEPT statement ( 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 suggestion that could be used.

0
source

More articles:


All Articles