SELECT TOP 2 MAX Value Group By SQL

select top 2 from each group sql
sql server select row with max value
mysql select row with max value group by
sql select max value for each group
sql select max value from multiple columns
sql max and min in one select
sql group by
sql select first record of each group

I have the following table:

-----------------------------------------
xDate        xItem       xCount
-----------------------------------------
2018-01-01   A           100
2018-01-01   B           200
2018-01-01   D           500
2018-01-02   C           200
2018-01-02   E           800

I want to select TOP 2 value for each date on the MAX value of xCount field. So, the result should be:

-----------------------------------------
xDate        xItem      xCount
-----------------------------------------
2018-01-01   D          500
2018-01-01   B          200
2018-01-02   E          800
2018-01-02   C          200

Does anyone have an idea for this case? Cheers,

You can try to use RANK window function, if there more the two row have same xCount then you want to get them all.

You can try to use dense_rank instead of RANK

SELECT xDate,xItem,xCount
FROM (
    SELECT *,RANK() OVER(PARTITION BY xDate ORDER BY xCount DESC) rn
    FROM T
) t1
WHERE t1.rn <= 2

SQL MAX() function with GROUP by, ORDER by, BY clause along with the SQL MAX() can be used to find the maximum value of SELECT working_area, MAX(commission) FROM agents GROUP BY working_area; 2. the group should be arranged in alphabetical order, Here's my question: for the first example "Max() function with Group by" how  Select the top N rows from each group. This is a slightly harder problem to solve. Finding a single row from each group is easy with SQL’s aggregate functions (MIN(), MAX(), and so on). Finding the first several from each group is not possible with that method because aggregate functions only return a single value.

You can use ROW_NUMBER() and partition it on the basis of xDate and order by xCount to get what you want.

select X.xDate, 
       X.xItem, 
       X.xCount
  from 
       (select xDate, 
               xItem, 
               xCount, 
               row_number() over (partition by xDate order by xCount desc) rank_of_count
         from table_name) X
 where rank_of_count < 3

Get second highest value in a table, Assuming the highest value only occurs once, another way would be to use OFFSET (SQL Server To get the second highest distinct value in the table you can use. SELECT MIN(value) FROM (SELECT DISTINCT TOP (2) value get the 2nd highest value and you have duplicates, you might want to group  T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER() Posted on July 11, 2015 Written by Andy Hayes 17 Comments I’ve recently been working on a data migration project and have found myself utilizing the ROW_NUMBER() function in SQL Server a lot.

Well I would not use any function. it looks pretty straight forward to me. The quickest would be

SELECT * FROM   #temp s
WHERE ( SELECT  COUNT(*)  FROM    #temp  f WHERE f.xDate = s.xDate AND  f.xCount >= s.xCount ) <= 2
Order by xDate, xCount desc

Check the full sample code here:

create table #temp (xDate datetime, xItem nvarchar(max), xCount int);

insert into #temp
select
'2018-01-01','A', 100 union all 
select '2018-01-01','B', 200 union all 
select '2018-01-01','D', 500 union all 
select '2018-01-02','C', 200 union all 
select '2018-01-02','E', 800


SELECT * FROM   #temp s
WHERE ( SELECT  COUNT(*)  FROM    #temp  f WHERE f.xDate = s.xDate AND  f.xCount >= s.xCount ) <= 2
Order by xDate, xCount desc

drop table #temp;

SQL SELECT TOP, LIMIT, ROWNUM, The SELECT TOP clause is used to specify the number of records to return. 2, Ana Trujillo Emparedados y helados, Ana Trujillo, Avda. de la Constitución  I want to select TOP 2 value for each date on the MAX value of xCount field. So, the result should be:----- xDate xItem xCount ----- 2018-01-01 D 500 2018-01-01 B 200 2018-01-02 E 800 2018-01-02 C 200

Another method is to use TOP, CROSS APPLY.

;WITH CTE AS
(
select 
distinct xDate
from Your_Table
)
SELECT 
T.xDate,
T1.xItem,
T1.xCount
FROM CTE T
CROSS APPLY (SELECT TOP 2 xItem,xCount FROM Your_Table WHERE xDate=T.xDate ORDER BY xCount DESC ) T1

SQL MIN() and MAX() Functions, The MAX() function returns the largest value of the selected column. 4, Chef Anton's Cajun Seasoning, 2, 2, 48 - 6 oz jars, 22. 5, Chef Anton's Gumbo Mix, 2, 2​  Select Rows with Maximum Value on a Column Example 2. In this example, we will show how to select rows with max value along with remaining columns. It is useful if you want to return the remaining columns (non-group by columns). For this example, we used the Inner Join to join the employee table with itself.

Just another one suggestion using DENSE_RANK():

DECLARE @FooTable TABLE
(
    xDate VARCHAR(25),
    xItem VARCHAR(10),
    xCount INT  
)

INSERT INTO @FooTable
(
    xDate,
    xItem,
    xCount
)
VALUES
  ('2018-01-01',   'A',           100)
, ('2018-01-01',   'B',           200)
, ('2018-01-01',   'D',           500)
, ('2018-01-02',   'C',           200)
, ('2018-01-02',   'E',           800)

SELECT 
s.*
FROM 
(
    SELECT 
      ft.xDate
    , ft.xItem
    , ft.xCount
    --, ROW_NUMBER() OVER(PARTITION BY ft.xDate ORDER BY ft.xCount DESC) rn
    , DENSE_RANK() OVER (PARTITION BY ft.xDate ORDER BY ft.xCount desc) dr
    FROM @FooTable ft
)s
WHERE s.dr < 3

SQL Server SELECT TOP By Practical Examples, This tutorial shows you how to use the SQL Server SELECT TOP statement to limit can have other clauses such as WHERE , JOIN , HAVING , and GROUP BY . The WITH TIES allows you to return more rows with values that match the last if two or more products have the same prices as the most expensive product,  SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

How to Select the First/Least/Max Row per Group in SQL, All involve two steps: finding the desired value of price , and then selecting the rest of the row based on that. One common solution is a so-called  In this article we have discussed how SQL HAVING CLAUSE can be used along with the SQL MAX() to find the maximum value of a column over each group. The SQL HAVING CLAUSE is reserved for aggregate function. The usage of WHERE clause along with SQL MAX() have also described in this page.

MAX, Returns the maximum value. Returns the largest, or maximum, value of expr . in the set, so MAX() may produce a different highest result than ORDER BY DESC. From MariaDB 10.2.2, MAX() can be used as a window function. ('​Tatiana', 'Tuning', 83); SELECT name, MAX(score) FROM student GROUP BY name;  You can use the steps in this article for any query where you need to select rows with MAX value for a column in Oracle SQL. Step 1 – Find Max Value for Groups. We can skip all the way to the end to get the query that you need. But, I thought I should explain how you get there, because it can help you in the future when you write other queries.

MySQL MAX Function, The MySQL MAX() function returns the maximum value in a set of values. The MAX() function comes in handy in many cases such as finding the greatest number, the most SELECT customerNumber, MAX(amount) FROM payments GROUP BY All MySQL tutorials are practical and easy-to-follow, with SQL script and  The SQL GROUP BY Statement. The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. GROUP BY Syntax

Comments
  • Understood, so if the xCount value has more than 2 records, it will show as the third record. Right?