CODEWITHSUNDEEP
phpmysqljoin
mhopkins321
mhopkins321

Reputation: 3073

Select top 10 most recent things using mysql

I am hoping to get the ten most recent patches submitted (top 10 patchID desc) Starting with the below query, I am getting an error on the 10. The error is syntax error, unexpected NUM, expecting END_OF_INPUT, or ';'. Would could be causing this error in the below query?

SELECT TOP 10 synth.*
FROM (
    SELECT p.`id`, 
        p.`patchName`,
        p.`description`,
        p.`tfsID`,
        p.`codeRelease`,
        s.`name`, 
        d.`deployStatus`, 
        d.`deployedDate`,
        t.`testedStatus`,
        t.`testedDate`
    FROM `patches` AS p 
    JOIN `deployed` AS d ON p.`id` = d.`PatchID`
    JOIN `servers` AS s ON d.`serverID` = s.`id`
    JOIN `tested` AS t ON p.`id` = t.`patchID`
) synth
ORDER BY synth.id DESC

Upvotes: 0

Views: 468

Answers (1)

Wolph
Wolph

Reputation: 80031

The TOP 10 is a MS SQL specific thing, in MySQL they use LIMIT instead.

SELECT p.`id`, 
    p.`patchName`,
    p.`description`,
    p.`tfsID`,
    p.`codeRelease`,
    s.`name`, 
    d.`deployStatus`, 
    d.`deployedDate`,
    t.`testedStatus`,
    t.`testedDate`
FROM `patches` AS p 
JOIN `deployed` AS d ON p.`id` = d.`PatchID`
JOIN `servers` AS s ON d.`serverID` = s.`id`
JOIN `tested` AS t ON p.`id` = t.`patchID`
ORDER BY p.`id` DESC
LIMIT 10

The syntax for limiting varies quite a bit per server, here's an overview:

Starting at row 100 showing 10 results (i.e. 101, 102, 103...)

PostgreSQL, MySQL and DB2: SELECT * FROM table LIMIT 10 OFFSET 100
Informix: SELECT SKIP 100 FIRST 10 FROM table 
Microsoft SQL Server and Access: SELECT TOP 10 * FROM table -- skipping the offset here as it's a pain, search for it if you need it :)
Oracle: SELECT * FROM (SELECT *, ROW_NUMBER() OVER ORDER BY id) rnk FROM table) WHERE rnk BETWEEN 100 AND 110

Upvotes: 8

Related Questions