Db2 - Returning the Top 5 of Each Category

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

• maintain status in 2 places
• preserve status history
• ...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.

mySQL Returning the top 5 of each category

You have to use side effecting variables for this

SELECT profilename, name
FROM
(
    SELECT m.profilename, s.name,
        @r:=case when @g=m.profilename then @r+1 else 1 end r,
        @g:=m.profilename
    FROM (select @g:=null,@r:=0) n
    cross join menus m 
    left join menuitems s on m.menuid = s.menuid
) X
WHERE r <= 5

Select from each group the last n rows orders by date in DB2

You can't do this with a simple order by clause; you need to use an OLAP function:

with ordered as (
  select group, 
         date, 
         row_number() over (partition by group order by date desc) as date_rank
    from mytable
)
select group, date 
  from ordered 
  where date_rank <= 10

DB2 SQL - Limit the number of groups returned

You can use dense_rank(). I think you want:

select t.*
from (select t.*,
             dense_rank() over (order by requestId) as seqnum
      from t
     ) t
where seqnum <= 3;

DB2: select all rows of data when multiple categories types of a column for each primary ID is found

In your first query you've used IN clause. That clause has translated as conditionA OR conditionB, so if you have only one of two brands, the query is satisfacted too.

You can use a double exists subquery, one for Honda, one for Ford related in AND

I suppose ID field is not a primary key for your table (has duplicated)

Try this:

select t1.*
from mytable t1
where exists(
    select 'hasford'
    from mytable t2
    where t2.id = t1.id
   and t2.car = 'Ford'
)
and exists(
    select 'hashonda'
    from mytable t3
    where t3.id = t1.id
   and t3.car = 'Honda'
)

---

Related Topics