CODEWITHSUNDEEP
sql-server
Venkat
Venkat

Reputation: 2156

select particular rows from a table

I have some of following fields in the quotes table : quotesid is the primarykey

+----------+----------------+-----------------------+
| QuotesID |   QuotesDesc   |    QuotesAuthor       |
+----------+----------------+-----------------------+
|      300 | my first quote   | william whakespeare |
|      301 | my second quote  | william shakespeare |
|      302 | my third quote   | william shakespeare |
|      303 | my 4th quote     | william shakespeare |
|      304 | my fifth quote   | william shakespeare |
|      305 | my sixth quote   | william shakespeare |
|      402 | my seventh quote | william shakespeare |
|      412 | my eighth quote  | william shakespeare |
+----------+----------------+-----------------------+

I have a query like this select * from quotestable where QuotesAuthor ='william shakespeare'.

My requirement is in my webpage if i display the quotes having quoteid = 300, i want the next 6 quotes to be displayed in an iframe. ie., 301, 302, 303, 304, 305, 402.

if my webpage displays quotes = 304 i want the next 6 quotes as 305,402,412, 300, 301, 302 (if it ends start from the beginning)

Is there a query which can give me all the 6 required rows?

Upvotes: 1

Views: 79

Answers (3)

dd4711
dd4711

Reputation: 799

Another, more trivial approach is to apply an artificial order attribute.

For every QuotesID we mark if the id is bigger or smaller than the id we start from. We then sort the set by this attribute, followed by the other sort criteria.

Out of this set we take the first N records.

Change the where clause accordingly wether you want to include your starting id (no where clause) or not QuotesID <> 300.

Your query might look like this

select top 6 
    case when QuotesID < @startid then 1 else 0 end orderbit
    ,* 
from @Quotes
where QuotesID <> 300
order by orderbit, QuotesID

A complete example:

DECLARE @startid INT = 304;

DECLARE @Quotes TABLE 
    (QuotesID int, QuotesDesc varchar(20), QuotesAuthor varchar(20));

INSERT INTO @Quotes(QuotesID, QuotesDesc, QuotesAuthor)
VALUES
    (300, 'myfirstquote', 'williamshakespeare'),
    (301, 'mysecondquote', 'williamshakespeare'),
    (302, 'mythirdquote', 'williamshakespeare'),
    (303, 'my4thquote', 'williamshakespeare'),
    (304, 'myfifthquote', 'williamshakespeare'),
    (305, 'mysixthquote', 'williamshakespeare'),
    (402, 'myseventhquote', 'williamshakespeare'),
    (412, 'myeighthquote', 'williamshakespeare');

select top 6 
    case when QuotesID < @startid then 1 else 0 end orderbit
    ,* 
from @Quotes
where QuotesID <> @startid
order by orderbit, QuotesID

which results in 

orderbit    QuotesID    QuotesDesc           QuotesAuthor
----------- ----------- -------------------- --------------------
0           305         mysixthquote         williamshakespeare
0           402         myseventhquote       williamshakespeare
0           412         myeighthquote        williamshakespeare
1           300         myfirstquote         williamshakespeare
1           301         mysecondquote        williamshakespeare
1           302         mythirdquote         williamshakespeare

Upvotes: 1

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

DECLARE @QuotesID INT = 304;

DECLARE @Table1 TABLE 
    ([QuotesID] int, [QuotesDesc] varchar(14), [QuotesAuthor] varchar(18));

INSERT INTO @Table1
    ([QuotesID], [QuotesDesc], [QuotesAuthor])
VALUES
    (300, 'myfirstquote', 'williamshakespeare'),
    (301, 'mysecondquote', 'williamshakespeare'),
    (302, 'mythirdquote', 'williamshakespeare'),
    (303, 'my4thquote', 'williamshakespeare'),
    (304, 'myfifthquote', 'williamshakespeare'),
    (305, 'mysixthquote', 'williamshakespeare'),
    (402, 'myseventhquote', 'williamshakespeare'),
    (412, 'myeighthquote', 'williamshakespeare');

SELECT QuotesID
    , QuotesDesc
    , QuotesAuthor
    , ISNULL((  SELECT TOP (1) QuotesID
                FROM @Table1 AS T2
                WHERE T2.QuotesAuthor = T1.QuotesAuthor
                AND T2.QuotesID > T1.QuotesID
                ORDER BY QuotesID ASC)
            , MIN(QuotesID) OVER (PARTITION BY T1.QuotesAuthor)) AS NextQuotesID
    , ISNULL((  SELECT TOP (1) QuotesID
                FROM @Table1 AS T3
                WHERE T3.QuotesAuthor = T1.QuotesAuthor
                AND T3.QuotesID < T1.QuotesID
                ORDER BY QuotesID DESC)
            , MAX(QuotesID) OVER (PARTITION BY T1.QuotesAuthor)) AS PrevQuotesID
FROM @Table1 AS T1
CROSS APPLY (SELECT CASE WHEN QuotesID >= @QuotesID THEN 0 ELSE 1 END AS OrderColumn) AS T
WHERE QuotesAuthor = 'williamshakespeare'
ORDER BY OrderColumn, QuotesID;

The query works like that: ORDER BY makes a check on how to order, if your QuotesID is equal or higher than your current @QuotesID, it will rank them first, if they're less than @QuotesID, they will be ranked second, then it will sort by QuotesID in ascending order.

Result with @QuotesID = 304;

+----------+----------------+--------------------+--------------+--------------+
| QuotesID |   QuotesDesc   |    QuotesAuthor    | NextQuotesID | PrevQuotesID |
+----------+----------------+--------------------+--------------+--------------+
|      304 | myfifthquote   | williamshakespeare |          305 |          303 |
|      305 | mysixthquote   | williamshakespeare |          402 |          304 |
|      402 | myseventhquote | williamshakespeare |          412 |          305 |
|      412 | myeighthquote  | williamshakespeare |          300 |          402 |
|      300 | myfirstquote   | williamshakespeare |          301 |          412 |
|      301 | mysecondquote  | williamshakespeare |          302 |          300 |
|      302 | mythirdquote   | williamshakespeare |          303 |          301 |
|      303 | my4thquote     | williamshakespeare |          304 |          302 |
+----------+----------------+--------------------+--------------+--------------+

Upvotes: 1

Torrents
Torrents

Reputation: 585

EDIT: I missed the last part of your question, where you said to start over if you reach the end. It was a bit unclear to me if you want the first row returned along with the following 6, but if not just change it from top 7 to top 6. This query is a bit easier than the other answer and avoids the queries in the SELECT clause and the Cross Apply.

DECLARE @Quotes TABLE (
    QuotesID INT, 
    QuotesDesc VARCHAR(30), 
    QuotesAuthor VARCHAR(30));

INSERT INTO @Quotes
    (QuotesID, QuotesDesc, QuotesAuthor)
VALUES
    (300, 'my first quote', 'William Shakespeare'),
    (301, 'my second quote', 'William Shakespeare'),
    (302, 'my third quote', 'William Shakespeare'),
    (303, 'my 4th quote', 'William Shakespeare'),
    (304, 'my fifth quote', 'William Shakespeare'),
    (305, 'my sixth quote', 'William Shakespeare'),
    (402, 'my seventh quote', 'William Shakespeare'),
    (412, 'my eighth quote', 'William Shakespeare');

DECLARE @QuotesID INT = 304;
DECLARE @Author varchar(24) = 'William Shakespeare';

SELECT TOP 7
    *
FROM (
        select TOP 7 *
        from @Quotes T1
        where T1.QuotesAuthor = @Author
            AND T1.QuotesID >= @QuotesID
        order by T1.QuotesID

        UNION ALL

        SELECT TOP 7 *
        FROM @Quotes T2
        WHERE T2.QuotesAuthor = @Author
            AND T2.QuotesID < @QuotesID
        ORDER BY T2.QuotesID
    ) X

Result:

+----------+------------------+---------------------+
| QuotesID |    QuotesDesc    |    QuotesAuthor     |
+----------+------------------+---------------------+
|      304 | my fifth quote   | William Shakespeare |
|      305 | my sixth quote   | William Shakespeare |
|      402 | my seventh quote | William Shakespeare |
|      412 | my eighth quote  | William Shakespeare |
|      300 | my first quote   | William Shakespeare |
|      301 | my second quote  | William Shakespeare |
|      302 | my third quote   | William Shakespeare |
+----------+------------------+---------------------+

Upvotes: 1

Related Questions