Achive top 3 only

Ernesto
Ernesto used Ask the Experts™
on
top 3 only query

Hi experts
how to achive


          SELECT top 3
  AVG(cast(CASE
    WHEN el = 'was' THEN ml
    ELSE mv
  END AS float))
FROM mlbscores
WHERE (el = 'was' OR ev = 'was') AND LEAGE = 'NHL'  order by ident desc

regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You are ordering by ident so that is what the TOP will return.

What top 3 are you looking for?

Based on the previous question I'm guessing top 3 averages.

Try this:
SELECT top 3
  AVG(cast(CASE
    WHEN el = 'was' THEN ml
    ELSE mv
  END AS float))
FROM mlbscores
WHERE (el = 'was' OR ev = 'was') AND LEAGE = 'NHL'  order by AVG(cast(CASE
    WHEN el = 'was' THEN ml
    ELSE mv
  END AS float)) desc

Open in new window

Author

Commented:
No
most recent values most recent games
i have a field id that is ident that goes  as a example (1) oldest (10) recent
want 10,9,8
regards

Author

Commented:
your same query but evaluate not all the database only the most recent 3 games
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What does the query you posted return?

Can you provide sample data with the expected results?

Author

Commented:
is the same
only the first 3 records

Author

Commented:
COL 5      CAL       3      03/10/2019      2019            25457                              NHL
CAL      3      LA       4      08/10/2019      2019            25867                              NHL
DAL      2      CAL       3      10/10/2019      2019            25975                              NHL
VEG      6      CAL       2      12/10/2019      2019            26125                              NHL
CAL      3      PHI       1      15/10/2019      2019            26349                              NHL
SJ      3      CAL       1      13/10/2019      2019            26259                              NHL
CAL      3      VAN 0      05/10/2019      2019            25624                               NHL

only top 3
the same query but just 3 records

Author

Commented:
but order by ident witch is the 7 col
WHERE (el = 'was' OR ev = 'was') AND LEAGE = 'NHL'
AND ident IN (SELECT TOP 3 ident FROM ...)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't know what that sample data is supposed to tell me.  I see no 'was' so I cannot set up a test case that matches the SQL you posted.

I will also need the expected results from any sample data you provide.

What I'm after is a test case I can use the SQL you posted so I can make sure I test it to achieve the results you provide.

Author

Commented:
you right
im going to isolate the top 3 in a view and its done
regards

Author

Commented:
tsm guys
i isolate the values i needed in a view

ALTER VIEW MLBV AS select top 3 * from mlbscores WHERE (ev ='was'  OR el ='was')AND LEAGE = 'NHL' order by ident DESC

and execute

SELECT
  AVG(cast(CASE
    WHEN el = 'was' THEN ml
    ELSE mv
  END AS float))
FROM mlbscores
WHERE (el = 'was' OR ev = 'was') AND LEAGE = 'NHL'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial