CODEWITHSUNDEEP
phpmysqlsql
Andre
Andre

Reputation: 273

Mysql Select Second Row

I have a mysql question.

I have a news section on my website, and I want to display the two latest items. If I do:

SELECT * FROM nieuws ORDER BY id DESC LIMIT 1

it selects the latest item, and now I want to select the second to last item.

Do you guys know how to do it?

/// EDIT

Now it doesn't work, here's my code: (I have connect included ;) )

            $select = mysql_query("SELECT * FROM nieuws ORDER BY id DESC LIMIT 1");
            while($row = mysql_fetch_assoc($select)) {
            $datum = $row['time'];
            $titel = $row['title'];
            $bericht = $row['message'];
            ?>
            <div class="entry">

                <span class="blue date"><?php echo "$datum"; ?></span>
                <h3><?php echo "$titel"; ?></h3>
                <p><?php echo "$bericht"; ?></p> <br />
            </div><!-- end of entry --> <?php } ?>
            <?php 
            $select2 = mysql_query("SELECT * FROM nieuws ORDER BY id DESC LIMI 1, 1");
            while($row2 = mysql_fetch_assoc($select2)) {
                $datum = $row2['time'];
                $titel = $row2['title'];
                $bericht = $row2['message'];
                ?>
            <div class="entry">
                <span class="green date"><?php echo "$datum"; ?> </span>
                <h3><?php echo "$titel"; ?></h3>
                <p><?php echo "$bericht"; ?></p>
            </div> <!-- end of entry --> <?php } ?>
        </div><!-- end of news -->

Upvotes: 24

Views: 62137

Answers (5)

Rich Adams
Rich Adams

Reputation: 26574

If you want to display the latest two items, then you can get both at the same time by limiting to 2 instead of 1. This means it's only one database hit to get the information you need.

SELECT * FROM nieuws
  ORDER BY id DESC
  LIMIT 2

Or if you only want the second row, you can give an offset to the LIMIT, to tell it which row to start from, (Although if you get the first row in one query, then get the second in another, you're doing two database hits to get the data you want, which can affect performance).

SELECT * FROM nieuws
  ORDER BY id DESC
  LIMIT 1, 1

You can find out more information on how to use the LIMIT clause in the MySQL documentation

Upvotes: 8

saurabh
saurabh

Reputation: 31

SELECT * FROM table where id > (SELECT id FROM table order by id ASC limit 1,1) and id <= (select max(id) from table)

Upvotes: 2

Mohammad Ali Abdullah
Mohammad Ali Abdullah

Reputation: 331

SELECT *
FROM promotion
WHERE id = 16037
ORDER BY RankID ASC LIMIT 1,1

Upvotes: 0

Alexander Konstantinov
Alexander Konstantinov

Reputation: 5486

LIMIT can take two arguments:

SELECT ... LIMIT 1, 1

Upvotes: 8

antyrat
antyrat

Reputation: 27765

SELECT * FROM nieuws ORDER BY id DESC LIMIT 2 - selects last 2 items

SELECT * FROM nieuws ORDER BY id DESC LIMIT 1, 1 - selects only second item

Upvotes: 51

Related Questions