select all rows except top row

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

did you google?i just copied the title and search and found so many answers! here is one : http://stackoverflow.com/questions/6027125/selecting-the-row-of-table-except-the-first-one — Arash, Feb 22 '13 at 20:42

score 32 · Answer 1 · answered Feb 22 '13 at 20:45

32

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

answered Feb 22 '13 at 20:45

chrisb

2,200
1
20
23

That is a nice feature – Kaf Feb 22 '13 at 21:22
Aye. shame it took them so long to implement... mysql has had it for years :( – chrisb Feb 22 '13 at 21:24
Great way of doing! +1 – Aleksandr Fedorenko Feb 22 '13 at 21:40
1

But I am using SQL 2005...offset doesn't work. What is the workaround for that version? – D-Dawgg Feb 25 '13 at 18:42
ROW_NUMBER is the way to go in that case. See @bluefeet's answer above. – chrisb Feb 25 '13 at 20:24

Taryn · Answer 2 · 2013-02-22T21:01:24.853

6

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

edited Feb 22 '13 at 21:01

answered Feb 22 '13 at 20:41

Taryn

242,637
56
362
405

2

The correct `order by` statement is `order by DateTimePlayed desc`. – Gordon Linoff Feb 22 '13 at 21:01
1

@GordonLinoff you are right, fixed – Taryn Feb 22 '13 at 21:01

score 1 · Answer 3 · answered Feb 22 '13 at 21:28

1

already 'Chrisb' has given a very neat answer. But you can also try this one...

The EXCEPT operand (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.

answered Feb 22 '13 at 21:28

Som Poddar

1,428
1
15
22

1

The first subquery is neither filtered nor ordered. The result of the complete query may not be what the OP is after. – Andriy M Feb 22 '13 at 22:42
I needed the second row returned by a table-valued function this helped me straight ! – Samra Jul 12 '18 at 06:33

score 0 · Answer 4 · answered Feb 22 '13 at 20:45

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

select all rows except top row

4 Answers4

Linked