Sql Query to Select Bottom 2 from Each Category

SQL Query to select bottom 2 from each category

You could try this:

SELECT * FROM (
  SELECT c.*,
        (SELECT COUNT(*)
         FROM user_category c2
         WHERE c2.category = c.category
         AND c2.value < c.value) cnt
  FROM user_category c ) uc
WHERE cnt < 2

It should give you the desired results, but check if performance is ok.

How to select bottom N rows from each group - Oracle 11g

Try this:

WITH ordered 
     AS (SELECT qcd_outlet_code, 
                qcd_year, 
                qcd_quarter, 
                qcd_credit, 
                Row_number() 
                  over ( 
                    PARTITION BY qcd_outlet_code 
                    ORDER BY qcd_outlet_code, qcd_year DESC, qcd_quarter DESC) 
                AS rn 
         FROM   QTR_CREDIT_DATA) 
SELECT d.qcd_outlet_code    AS "Outlet_Code:string", 
       d.qcd_quarter 
       ||' ' 
       ||d.qcd_year         AS "MCT_quarter:string", 
       Nvl(d.qcd_credit, 0) AS "MCT_Total_Credits_Earned", 
       Row_number() 
                  over ( 
                    PARTITION BY qcd_outlet_code 
                    ORDER BY qcd_outlet_code, qcd_year, qcd_quarter)
                           AS "Display_Order:string" 
FROM   ordered d 
WHERE  rn <= 2; 

Select bottom n records and concatenate in same row

Select last 20 rows when ordered by id. Return in ASC order, with a single query

SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(val1, '') + '; ' +
            ISNULL(val2, '') + '; ' +
            ISNULL(val3, '') + '; ' +
            ISNULL(val4, 14), '')
        FROM 
        (
            SELECT top(20) *
            FROM table_x
            ORDER BY id DESC
        ) AS Latest_rec
        ORDER BY id ASC
        FOR XML PATH ('')), 1, 2, '') AS val;

Select Bottom 30% of Rows by Group in SQL Server 2017

This would do I think:-

WITH RecordCount AS (
    SELECT ParentID, ROUND(COUNT(1)*.3, 0) RecordCount -- To be selected
    FROM MyData
    GROUP BY ParentID
), SortedRecords AS (
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY ID DESC) RowIndex
    FROM MyData
)
SELECT d.*
FROM MyData d
    JOIN SortedRecords sr ON d.ID = sr.ID
    JOIN RecordCount rc ON d.ParentID = rc.ParentID AND sr.RowIndex <= rc.RecordCount;

Select top and bottom rows

Using a union is the only thing I can think of to accomplish this

select * from (select top(5) * from logins order by USERNAME ASC) a
union
select * from (select top(5) * from logins order by USERNAME DESC) b

SELECT BOTTOM without changing ORDER BY

You could just wrap it in another query and put in the order you require...

SELECT x.* 
FROM   (
       SELECT TOP 504 
              "date",
              price
       FROM   [dbo].[AssetRet]
       WHERE  asset = 'SP500'
       ORDER BY "date" DESC
       ) x 
ORDER BY x."date"

Does this alternative work?...you will need a later version of sql-server for the partition function RANK...

SELECT x."date",
       x.price
FROM   (
       SELECT "date",
              price,
              Rnk = RANK() OVER (ORDER BY "date" DESC)
       FROM  [dbo].[AssetRet]
       WHERE asset = 'SP500'
       ) x 
WHERE  x.Rnk <= 504
ORDER BY x."date" 

EDIT
Looks like this other answer pretty much covered your question

PostgreSQL: Query to retrieve the top 2 and bottom 2 rows including equals

One method is to use dense_rank():

SELECT PROD_ID, c
FROM (SELECT PROD_ID, COUNT(*) AS c,
             DENSE_RANK() OVER (ORDER BY COUNT(*) ASC) as seqnum_asc,
             DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum_desc
      FROM Orderlines
      GROUP BY Orderlines.PROD_ID
     ) o
WHERE seqnum_asc <= 2 OR
      seqnum_desc <= 2
ORDER BY c DESC;

Select number of rows from each category

Using Dynamic Top
In place of N you can use any number

SELECT Id,Content,Category,createdAt
  FROM (SELECT t.*,
               CASE 
                 WHEN @category != t.category THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.category AS var_category
          FROM Table1 t
          JOIN (SELECT @rownum := NULL, @category := '') r
      ORDER BY t.Category,t.createdAt DESC) x
 WHERE x.rank <= 'N'

In place of N you can use any number

Output

ID  Content Category    createdAt
6   test6   cat1    2018-03-26T18:23:17Z
2   test2   cat1    2018-03-26T18:22:46Z
5   test5   cat2    2018-03-26T18:23:13Z
4   test4   cat2    2018-03-26T18:23:11Z 

Live Demo

http://sqlfiddle.com/#!9/00ca02/20

---

Related Topics