Limit number of records and specify search criteria

you can also add the amount of values you want to select from as a parameter and then use top @parameterForNumberOFRows

additonally - I would suggest that you are better not using a case statement as part of the query - but using if statements in the SP to construct the order by statement - this should be much faster and more reliable.

What they are getting at is something like:

Declare @SQL VarChar(8000)

Set @SQL = 'Select ........ Order By '
Set @SQL = @SQL + '[' + @OrderBy '] '
If (@OrderDir = 'Desc') Set @SQL = @SQL + 'Desc'
Execute (@SQL)

It is still now coming together in my head.  

If I add TOP 100 and a user decides to sort by price asc, then I get the top 100 sorted by price asc instead of what I need which is the 100 newest items sorted by price asc, right?

you did not say if this is the the top 100 before or after the grouping

select * from
(
SELECT  top 100 name,address,city,state,zip,price,date
      FROM (table1 INNER JOIN table2 ON table1.id = table2.id)
      WHERE (table1.status = 2)
GROUP BY name,address,city,state,zip
      order by date desc)


      ORDER BY
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'price' THEN price
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'name' THEN name
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'date' THEN date
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'price' THEN price
                  END
            END
      DESC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'name' THEN name
                  END
            END
      DESC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'date' THEN date
                  END
            END
      DESC

"not", not "now"

I guess part of my problem is I don't really get grouping.

Ok StephenCairns, that looks like the direction I need to go.

Ugh, forgot the top:

Declare @SQL VarChar(8000)

Set @SQL = 'Select Top 100 ........ Order By '
Set @SQL = @SQL + '[' + @OrderBy '] '
If (@OrderDir = 'Desc') Set @SQL = @SQL + 'Desc'
Execute (@SQL)

For the record, whether you go with this dynamic SQL solution or the humunguous subquery solutions others are proposing, this will be Extremely Inefficient(TM). The SQL Server tries to optimize queries, and neither solution allows for this. To put it another way, you might be reduced to do something like

If (@OrderBy = 'blah')
  Select ...
Else If (@OrderBy = 'other blah')
  Select ...

Consider that fair warning  :-)

StephenCairns, when I make your recommended change, I get the error "Incorrect syntax near the keywork 'ORDER'.  The error is pointing to the ORDER BY statement right above the first CASE.

Hmm

try giving the sub query an alias
(
SELECT  top 100 name,address,city,state,zip,price,date
      FROM (table1 INNER JOIN table2 ON table1.id = table2.id)
      WHERE (table1.status = 2)
GROUP BY name,address,city,state,zip
      order by date desc) x

Wait, are expressions even allowed at that point?

[ Testing... ]

It does not seem to swallow any variant. lp: you might have to resort to my hack.

lpenrod
if that does not work you could pull the subquery out as a view and handle it that way.

SjoerdVerweij
wont your version just give the top 100 records ordered by whatever the parameter is rather than the most recent 100 records ordered by the parameter?

Stephen: Ugh. Yes, you are correct.

To do that, you would have to go something like

Declare @SQL VarChar(8000)

Set @SQL = 'Select Top 100 ........ Order By [WhatEverFieldDefinesWhichMakesEmRecent], '
Set @SQL = @SQL + '[' + @OrderBy '] '
If (@OrderDir = 'Desc') Set @SQL = @SQL + 'Desc'
Execute (@SQL)

Sjoerd
that would give the most recent 100 records ordered by date *and*  the required parameter, not the most recent 100 records ordered by the required parameter.  we need to get the subset of data first then order it.
I think probably using a view to get the subset then ordering it would be the way to go.

cheers

Sadly, you might as well be talking another language.  I am not understanding what I need to do.

Sorry
I thik we are geting sidetracked

create a view to get your subset of data

CREATE VIEW v_recentdata
AS

SELECT  top 100 name,address,city,state,zip,price,date
      FROM (table1 INNER JOIN table2 ON table1.id = table2.id)
      WHERE (table1.status = 2)
GROUP BY name,address,city,state,zip
      order by date desc  

GO


Then change your stored procedure to


CREATE PROCEDURE sp_newest
      (@orderby varchar(10),
      @orderdir varchar(4),
      @search varchar(100))
AS
SELECT name,address,city,state,zip,price,date
      FROM v_recentdata
      ORDER BY
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'price' THEN price
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'name' THEN name
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'date' THEN date
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'price' THEN price
                  END
            END
      DESC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'name' THEN name
                  END
            END
      DESC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'date' THEN date
                  END
            END
      DESC
GO

mm seems to me all are getting side tracked or ime just going nuts.
"I need to return only 100 records and the records need to be the 100 newest (based on date).
The user may choose to sort by price or name or date, but the records always have to be the 100 newest. "
1.  Your using a sp, so within the sp create a temp table picking up the 100 most recent records of the actual select condition.
2.   Output the resultset from your temp table according to the requested sort orders.

ie
CREATE PROCEDURE sp_newest
      (@orderby varchar(10),
      @orderdir varchar(4),
      @search varchar(100))
AS
begin
   -- Step 1
   SELECT  TOP 100 name,address,city,state,zip,price,date
     INTO  #tmp
      FROM table1
      INNER JOIN table2 ON (table1.id = table2.id)
      WHERE table1.status = 2
      ORDER BY date DESC
   -- Step 2  do the actual output
    SELECT * from #tmp
    ORDER BY
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'price' THEN price
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'name' THEN name
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'asc' THEN
                  CASE @orderby
                        WHEN 'date' THEN date
                  END
            END
      ASC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'price' THEN price
                  END
            END
      DESC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'name' THEN name
                  END
            END
      DESC,
            CASE @orderdir
                  WHEN 'desc' THEN
                  CASE @orderby
                        WHEN 'date' THEN date
                  END
            END
end


     
     
     

I will give them a try tonight.

Thanks!

SjoerdVerweij, I love loading the variable and then executing.  That is making the code a lot shorter and more readable for me.

But using everyones latest suggestions, I was receiving an error an error "Incorrect sytax near the keyword ORDER".  I was able to figure out (actually _agx_ did for me) that one of the SELECT's needs to be aliased (not sure if I am saying that right).  Here is the question:  http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22627398.html

Here is my code with some obfuscation:

DECLARE @SQL CHARACTER VARYING(8000)

SET @SQL ='SELECT Count(re_project_views.project_id) AS view_count,re_projects.screen_id,re_screens.screen_screen_name,project_name,project_posted_date,project_size_x,project_size_y,project_frame_rate,CONVERT(varchar,project_duration,14) AS project_duration,ae_version_name,project_linked_graphics,project_camera,project_lights,project_expressions,project_price,platform_name,pixel_aspect_ratio,re_projects.project_id,project_plugins' +
' FROM (SELECT TOP 100 screen_screen_name FROM ((((((re_projects INNER JOIN re_screens ON re_projects.screen_id = re_screens.screen_id)' +
' INNER JOIN re_platform ON re_projects.platform_id = re_platform.platform_id)' +
' INNER JOIN re_pixel_aspect_ratio ON re_projects.pixel_aspect_ratio_id = re_pixel_aspect_ratio.pixel_aspect_ratio_id)' +
' INNER JOIN re_ae_version ON re_projects.ae_version_id = re_ae_version.ae_version_id)' +
' LEFT JOIN re_project_rating_map ON re_projects.project_id = re_project_rating_map.project_id)' +
' INNER JOIN re_project_views on re_project_views.project_id = re_projects.project_id)' +
' WHERE (re_projects.status_id = 2)'
IF (@project_search <> '') SET @SQL = @SQL + ' AND (re_projects.project_search_items like ''%/,' + @project_search + '/,%'' escape ''/'')'
SET @SQL = @SQL + ' GROUP BY re_projects.screen_id,re_screens.screen_screen_name,project_name,project_posted_date,project_size_x,project_size_y,project_frame_rate,CONVERT(varchar,project_duration,14),ae_version_name,project_linked_graphics,project_camera,project_lights,project_expressions,project_price,platform_name,pixel_aspect_ratio,re_projects.project_id,project_plugins) AS T'

IF ((@orderby = 'price')) SET @SQL = @SQL + ' ORDER BY project_price ' + @orderdir
IF ((@orderby = 'profile')) SET @SQL = @SQL + ' ORDER BY screen_screen_name ' + @orderdir
IF ((@orderby = 'rating')) SET @SQL = @SQL + ' ORDER BY ISNULL(AVG(project_rating), 0) ' + @orderdir
IF ((@orderby = 'popular')) SET @SQL = @SQL + ' ORDER BY Count(re_project_views.project_id) ' + @orderdir
IF ((@orderby = 'uploaded')) SET @SQL = @SQL + ' ORDER BY project_posted_date ' + @orderdir

EXECUTE (@SQL)

GO

Both in this code and in the KarinLoos suggestion, I get the message:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 're_project_views' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 're_projects' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 're_screens' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 're_projects' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 're_project_views' does not match with a table name or alias name used in the query.

I feel like we are close.

Thanks, that got it working!

If there are no objections, I am going to award this question to StephenCairns.

But, SjoerdVerweij, I really appreciate your tip on loading the variable!  I love that I can PRINT the string to debug!  Greatly simplifies troubleshooting.

Hi lpenrod
Its fine by me if you want to split the points. Dont forget SjoerdVerweij other tip, dont drink cocktails before answering questions ;-)
cheers