CODEWITHSUNDEEP
sqlsql-server
Artur Peniche
Artur Peniche

Reputation: 479

How do I get top N distinct rows from a table with several repeated values?

I have the following table on Microsoft SQL server:

NumContr  SeqYear check
111111111   1   -0.50
111111111   2   -0.30
111111111   3    0.30
111111111   4    0.00
111111111   5   -40.00
111111111   6   -200.00
111111111   7   -100.00
111111111   8   -0.50
111111111   9   -0.30
111111111   10   0.30
111111111   11   0.00
111111111   12  -40.00
111111111   13  -200.00
111111111   14  -100.00
400247129   1    NULL
400247129   2    NULL
400247129   3    NULL
400247129   4    NULL
400247129   5    NULL
400247129   6    NULL
400247129   7    NULL
400247129   8    0.00
400247129   9    15254.05
400247129   10   15254.05
400247129   11   15254.05
400247129   12   0.00
400247129   13   0.00
400247129   14   0.00

I would like to have the top 7 SeqYears from each NumContr row. Is there any clean away to achieve this result set?

The table name is tb_FinancialPosition

    111111111   8   -0.50
    111111111   9   -0.30
    111111111   10  0.30
    111111111   11  0.00
    111111111   12  -40.00
    111111111   13  -200.00
    111111111   14  -100.00
    400247129   8   0.00
    400247129   9   15254.05
    400247129   10  15254.05
    400247129   11  15254.05
    400247129   12  0.00
    400247129   13  0.00
    400247129   14  0.00

Thanks in advance!

Upvotes: 1

Views: 64

Answers (2)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

Here is a another way to do this 

    select * from 
    (
      select * from 
      (
       select NumContr,SeqYear,`Check`
       from practise
       where NumContr ='111111111'
       order by SeqYear desc limit 7
      )t1 order by SeqYear asc 
    )table1


    Union all 

   select * from 
    (
      select * from 
     (
      select NumContr,SeqYear,`Check`
      from practise
      where NumContr ='400247129'
      order by SeqYear desc limit 7
     ) t2 order by SeqYear asc
   ) Table2

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

One way to do it is with variables:

SELECT NumContr, SeqYear, `check`
FROM (
  SELECT NumContr, SeqYear, `check`,
         @rn := IF(@num = NumContr, @rn + 1,
                   IF(@num := NumContr, 1, 1)) AS rn
  FROM tb_FinancialPosition
  CROSS JOIN (SELECT @rn := 0, @num := 0) AS vars
  ORDER BY NumContr, SeqYear DESC) AS t
WHERE t.rn <= 7

Demo here

In SQL Server it is easier to implement, since you have window functions:

SELECT NumContr, SeqYear, [check]
FROM (
   SELECT NumContr, SeqYear, [check],
          ROW_NUMBER() OVER (PARTITION BY NumContr 
                             ORDER BY SeqYear DESC) AS rn
   FROM tb_FinancialPosition) AS t
WHERE t.rn <= 7

Upvotes: 3

Related Questions