How can i select all other records without top five records?

Question

Location    TotalRevenue    LocationID

Orugodawatta    10059135.78 OR
Kohuwala    7058537.73  KH
Koswaththa  6717136.02  KW
Havelock Town   5748932.59  HT
Negombo         5193678.33  NG
Induruwa    3017552.74  IA
Absdhku         2254281.21      AB

I have a table in sql server 2008. how can i select all other rows without top 5 records? if my table had 100 records i can selece all other 95 records without top 5 records. please help me

What do you mean "without the top 5 records" ? Only the last 95 of the rows, or the first 95 ? — KarelG, Jul 23 '13 at 09:01
See http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — BorisOkunskiy, Jul 23 '13 at 09:02
SELECT top(5)Location, SUM(SellingPrice) AS 'Total Revenue', LocationID FROM BI_LocWiseTopItems WHERE (GRNDate BETWEEN '' AND GETDATE()) GROUP BY Location, LocationID ORDER BY 'Total Revenue' desc this is my query. it is returining only top 5 records. bt i want only all othre records without top 5. number of records are dynamically changing — user2220385, Jul 23 '13 at 09:03

score 1 · Answer 1 · answered Jul 23 '13 at 09:04

1

Try this.

SELECT * FROM MyTable WHERE LOCATION NOT IN (SELECT TOP 5 LOCATION FROM MyTable)

answered Jul 23 '13 at 09:04

Kosala W

2,133
1
15
20

score 1 · Answer 2 · answered Jul 23 '13 at 09:09

1

SELECT TOP (SELECT Count(*) - 5 FROM tableName WHERE YOUR_WHERE_CLAUSE) * 
  FROM tableName 
 WHERE YOUR_WHERE_CLAUSE
 ORDER BY COLUMN_NAME DESC

answered Jul 23 '13 at 09:09

Romesh

2,291
3
24
47

Nithesh Narayanan · Answer 3 · 2013-07-23T10:26:44.407

0

SELECT * FROM tableName 
EXCEPT (SELECT TOP(5)* FROM tableName  )

Your entire query. Have a try

SELECT Location,[Total Revenue],LocationID
FROM
  (
    SELECT Location, SUM(SellingPrice) AS 'Total Revenue', LocationID 
    FROM BI_LocWiseTopItems 
    WHERE (GRNDate BETWEEN '' AND GETDATE()) 
    GROUP BY Location, LocationID ORDER BY 'Total Revenue' desc 
  ) AS temp
EXCEPT  (
    SELECT top(5)Location, SUM(SellingPrice) AS 'Total Revenue', LocationID 
    FROM BI_LocWiseTopItems 
    WHERE (GRNDate BETWEEN '' AND GETDATE()) 
    GROUP BY Location, LocationID ORDER BY 'Total Revenue' desc 
 )

edited Jul 23 '13 at 10:26

answered Jul 23 '13 at 09:02

Nithesh Narayanan

11,481
34
98
138

Overkill. Upgrade to MSSQL 2011, which has an `OFFSET` clause :) – BorisOkunskiy Jul 23 '13 at 09:02
MSSQL 2011? It is 2012. But he didn;t mentioned the version – Nithesh Narayanan Jul 23 '13 at 09:03
And upgrading SQL server (with all the costs associated) is not overkill? – Mr47 Jul 23 '13 at 09:04
@Nithesh He did, 2008 does not have an `OFFSET`... @Mr47 Depends. If this is a one-time query, then yes, totally. But if it is meant to be used repeatedly, maybe by an application, then upgrading -- or maybe moving away from MSSQL altogether -- may be the right choice. – BorisOkunskiy Jul 23 '13 at 09:07
Did you try, your result will always be empty, wether the table is empty or if not: EXISTS will return `true` and nothing will be selected – bummi Jul 23 '13 at 09:07
Updated the answer with except. I think this will work properly – Nithesh Narayanan Jul 23 '13 at 10:27

score 0 · Answer 4 · answered Jul 23 '13 at 09:02

0

suppose you get Top 5 records by Query

select Top 5 * 
from Table_name 
order by Location desc

So to get your 95 records

select Top 100 * 
from Table_name 
order by Location desc
except
select Top 5 * 
from Table_name 
order by Location desc

answered Jul 23 '13 at 09:02

Prahalad Gaggar

11,389
16
53
71

score 0 · Answer 5 · answered Jul 23 '13 at 09:35

Select all in first CTE, select top 5 in second, and just use EXCEPT :

WITH CTE_ALL AS 
(
    SELECT 
            Location ,
            SUM(SellingPrice) AS [Total Revenue] ,
            LocationID
    FROM    BI_LocWiseTopItems
    WHERE   ( GRNDate BETWEEN '' AND GETDATE() )
    GROUP BY Location ,
            LocationID
)
, CTE_TOP5 AS 
(
    SELECT TOP 5 * FROM CTE_ALL 
    ORDER BY [Total Revenue]
)
SELECT * FROM CTE_ALL
EXCEPT
SELECT * FROM CTE_TOP5

SQLFiddle DEMO - Simplified for CTE

score 0 · Answer 6 · answered Jul 23 '13 at 10:25

; WITH top5 AS (
  SELECT TOP 5
         Location
       , TotalRevenue
       , LocationID
  FROM   your_table
  ORDER
      BY TotalRevenue DESC
)
SELECT Location
     , TotalRevenue
     , LocationID
FROM   your_table
EXCEPT
SELECT Location
     , TotalRevenue
     , LocationID
FROM   top5

How can i select all other records without top five records?

6 Answers6