问题描述:

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.

MS SQL

Use the TOP clause:

SELECT TOP (36)
...

Oracle

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)
相关阅读:
Top