How can we limit each specific value in a one column in SQL

Question

Sorry for if it is a silly question. I can order my column in ORDER BY function and I can use LIMIT function but it gives me only top value but I need each top value limitation.

So my dummy table is here:

Col-a   col-b
A        1001
B        1000
B        999
C        998
A        997
C        996
A        995
A        994
A        993
A        992
B        991
C        990

my expectation is (LIMIT 3;)

col_a    col_b                      col_a   col_b   col_c   col_d   col_e   col_f
A       1001                        A       1001     B       1000    C       998
B       1000                        A       997      B       999     C       996
B       999                         A       995      B       991     C       990
C       998               or
A       997          
C       996
A       995
B       991
C       990

I Used FIELD() but unfortunately I could not manage. How can we order that specific call.

ORDER BY function order order all values but I need take each value a specific number. When I use LIMIT function only cut top values Question is How can Order each different value in "1 Column"

EDIT: I tried

SELECT col_a,col_b FROM my_table 
order BY FIELD(col_b, 'A','B','C') limit 100;

See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Dec 19 '17 at 23:55
@Axis can you paste your desired output as well, I wasn't clear from ur question, sorry — zenwraight, Dec 20 '17 at 00:10

dstudeba · Answer 1 · 2017-12-20T00:24:05.977

There is likely a more efficient method but this will probably get you where you want to go:

SELECT group_a.col_a AS col_a
       ,group_a.col_b AS col_b
       ,group_b.col_a AS col_c
       ,group_b.col_b AS col_d
       ,group_c.col_a AS col_e
       ,group_c.col_b AS col_f
FROM (SELECT col_a, col_b FROM my_table WHERE col_a = 'A' ORDER BY col_b DESC LIMIT 100) as group_a,
     (SELECT col_a, col_b FROM my_table WHERE col_a = 'B' ORDER BY col_b DESC LIMIT 100) as group_b,
     (SELECT col_a, col_b FROM my_table WHERE col_a = 'C' ORDER BY col_b DESC LIMIT 100) as group_c

score 0 · Answer 2 · answered Dec 20 '17 at 00:50

0

Gives you what you need , though in a little different format . Please check and let me know if this works for you

select COL-A,MIN(COL-B),lead(MIN(COL-B),1) over(order by MIN(COL-B)),lead(MIN(COL-B),2) over(order by MIN(COL-B)) from audit.latest_job_instance where COL-B is not null GROUP BY COL-A

answered Dec 20 '17 at 00:50

sqluser

176
7

Please read the edit help about formatting code. You can see the formatted output below the edit box. – philipxy Dec 20 '17 at 09:59

Krupa · Answer 3 · 2017-12-20T11:33:59.823

-1

Try this one: give rank based on partition column-a and order by column-b desc. Select rows where rank <=3 This query will provide expected result(1) means 2 columns (col_a and col_b)

select [col-a], [col-b]
from
( select [col-a], [col-b], row_number() over(partition by [col-a] order by [col-b] desc) rnk from table2 
) tbl
where rnk <= 3

edited Dec 20 '17 at 11:33

answered Dec 20 '17 at 09:59

Krupa

437
3
12

How can we limit each specific value in a one column in SQL

3 Answers3