i have 37 rows in a table how can i show 36 rows
SELECT c.CommonContentId,c.Content_Code
,c.Content_Name
,c.Seo_Name
,c.[Description]
,c.Link
,c.Page_Path
,c.ContentType_Code
,c.Creation_Date
,c.Is_Active
,c.Last_Update_Date
,c.Category_Code
,c.Category_Code_Style
,c.Content_Script
,c.Content_Website
,c.Logo_Path
,c.Case_Study
,c.SortOrder
,c.Tags
,c.Vl_Page_Image_Url
,c.Is_Show_Vl_Pages,
,ct.ContentType_Name
,cat.Category_Name
,cat1.Category_Name as Category_Name_Style
,ROW_NUMBER() over (ORDER BY c.ContentType_Code ASC, c.Content_Code DESC) as Record_No
,CASE WHEN ROW_NUMBER() over (ORDER BY c.ContentType_Code ASC, c.Content_Code DESC) % 3 = 0
THEN '' ELSE '' END as Class
,c.SortOrder
, Case WHEN len(logo_path) > 1
then 'imagePadded'
else '' end as Tetimonial_Image_Css
,Case when c.Link like '%vidyard%'
then 'http://play.vidyard.com/'+c.Page_Path+''
else 'http://vimeo.com/'+c.Page_Path+'' end as videolinks
FROM Content c
INNER JOIN ContentType ct ON c.ContentType_Code = ct.ContentType_Code
LEFT OUTER JOIN Category cat ON c.Category_Code = cat.Category_Code
LEFT OUTER JOIN Category cat1 ON c.Category_Code_Style = cat1.Category_Code
WHERE c.Is_Active = 1
It is not quite clear if you want an answer for Oracle or MS SQL server, as your question is tagged both with PL/SQL and SQL-server.
As far as I know, there is no cross-vendor syntax to achieve that goal.
Use the TOP
clause:
SELECT TOP (36)
...
For Oracle, one way to do it is simply using the pseudo-column ROWNUM
:
SELECT ....
WHERE ROWNUM <= 36
Starting with Oracle 12c (I think), you might use the FETCH
clause:
SELECT ...
FETCH FIRST 36 ROWS ONLY;
I will use CTE. Then skip the last row. This will work for table with any number of records, and truely "skip last record in sql server table" without using any magic number.
WITH CTE AS
(
-- Your SQL Statement here...
)
Select * from CTE
where Record_No <> (SELECT Count(*) from CTE)