How to select all records from a table except for the first 5 records?

Question

I am new to querying a tables in mysql and also using a stored procedure. I have this query below I want to query all data that is >5. Is there a query something like this select * from table Except top 5 so it will start from data #6 I search some code but I only find is a direct query w/o a condition.

SELECT *
    FROM dbo.PersonSkill INNER JOIN
    dbo.SpecialSkill ON dbo.PersonSkill.SpecialSkillsId = dbo.SpecialSkill.SpecialSkillsId
WHERE dbo.PersonSkill.PersonId = @PersonId

score 2 · Answer 1 · answered Nov 14 '16 at 08:24

2

You can simply use LIMIT parameter.

LIMIT 5, 10

First parameter (5) - start from

Second parameter (10) - how many rows to select

answered Nov 14 '16 at 08:24

3y3skill3r

889
1
8
27

how can I use that in my query? sorry new here. I want to select all the rest of the data. – Trafalgar D Law Nov 14 '16 at 08:24
Read this documentation: http://dev.mysql.com/doc/refman/5.7/en/select.html this site is not "I will write your code for free" Learn how it works. That is only one way how to make it and learn it. – 3y3skill3r Nov 14 '16 at 08:28
`LIMIT` only makes good sense when used along with `ORDER BY`. So you should add an ordering to the query. – Tim Biegeleisen Nov 14 '16 at 08:29

jai dutt · Answer 2 · 2016-11-14T08:48:42.693

2

Try this

SELECT     *
    FROM         dbo.PersonSkill INNER JOIN
                          dbo.SpecialSkill ON dbo.PersonSkill.SpecialSkillsId = dbo.SpecialSkill.SpecialSkillsId
    WHERE     dbo.PersonSkill.PersonId = @PersonId   and unique_id not in (SELECT   top 5  unique_id
    FROM         dbo.PersonSkill INNER JOIN
                          dbo.SpecialSkill ON dbo.PersonSkill.SpecialSkillsId = dbo.SpecialSkill.SpecialSkillsId
    WHERE     dbo.PersonSkill.PersonId = @PersonId )

edited Nov 14 '16 at 08:48

answered Nov 14 '16 at 08:35

jai dutt

756
6
13

the keyword LIMIT is in incorrect syntax. I am using sql server 2008 r2 – Trafalgar D Law Nov 14 '16 at 08:40
Really, then you should amend your tag as strawberry suggested. – P.Salmon Nov 14 '16 at 08:45

score 2 · Accepted Answer · edited May 23 '17 at 12:32

2

SQL Server doesnt have LIMIT or OFFSET Function but you can simulate this behaviour like this , and the result is like :

;WITH Results_CTE AS
(
    SELECT
        dbo.PersonSkill.*, dbo.SpecialSkill.SpecialSkillsId, dbo.SpecialSkill.OtherCol
        ROW_NUMBER() OVER (ORDER BY @PersonId) AS 'RowNum'
    FROM dbo.PersonSkill 
    INNER JOIN dbo.SpecialSkill 
    ON dbo.PersonSkill.SpecialSkillsId = dbo.SpecialSkill.SpecialSkillsId
    WHERE dbo.PersonSkill.PersonId = @PersonId 
)
SELECT *
FROM Results_CTE
WHERE RowNum >5

edited May 23 '17 at 12:32

Community

1
1

answered Nov 14 '16 at 08:52

Aroniaina

1,202
11
29

What does Results_CTE means?. – Trafalgar D Law Nov 14 '16 at 08:56
Alias for temporary table – Aroniaina Nov 14 '16 at 08:56
i got this error `The column 'SpecialSkillsId' was specified multiple times for 'Results_CTE'. ` – Trafalgar D Law Nov 14 '16 at 09:12
replace `select *` by specifics collumn like `select dbo.PersonSkill.*,dbo.SpecialSkill.SpecialSkillsId, dbo.SpecialSkill.OtherCol, ROW_NUMBER() OVER (ORDER BY @PersonId) AS 'RowNum' ..... ` – Aroniaina Nov 14 '16 at 09:16

score 1 · Answer 4 · answered Nov 14 '16 at 11:16

Try this

SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY @PersonId) as rn
FROM dbo.PersonSkill INNER JOIN
dbo.SpecialSkill ON dbo.PersonSkill.SpecialSkillsId = dbo.SpecialSkill.SpecialSkillsId
WHERE dbo.PersonSkill.PersonId = @PersonId)A where A.rn>5

How to select all records from a table except for the first 5 records?

4 Answers4