Restrictions on SQL order

Question

Still new to asking questions here, so this is my second attempt at a problem I face.

I have a table that is already ordered by Score. Every record can be one of 3 types. I would like to reorder the table by Score but impose an additional restriction on the mix, in every 10 records. Particularly, there is a specific mix consisting of each of the 3 types.

In the example below, I want to have at least 2 of type C and at most 2 of type A

What I have

------------------------------------------
ID      Score      Type
------------------------------------------
1       100         A
2       99          B
3       97          B
4       92          A
5       91          C
6       85          A
7       83          B
8       81          B
9       75          B
10      70          B
11      65          A
12      61          C
13      59          B

What I want

------------------------------------------
ID      Score      Type
-----------------------------------------
1       100         A
2       99          B
3       97          B
4       92          A
5       91          C
7       83          B
8       81          B
9       75          B
10      70          B
12      61          C

I don't think this can be solved by Group By or usort in an efficient manner. My initial solution was to export to PHP and do the reordering there.

If you're using SQL Server > 2005, I think you can use RANK to get at what you want. See http://msdn.microsoft.com/en-us/library/ms176102.aspx for more details. — cciotti, Jan 04 '13 at 17:46

A B · Answer 1 · 2013-01-04T18:07:11.977

0

I am assuming you want all rows with TYPE=C returned and all rows with TYPE=B returned, but only at most 2 rows with TYPE=A returned.

This will work in ORACLE - ROWNUM can be used in a subquery to limit the number of rows.

(In another database ROWNUM might not even be implemented)

SELECT 
T.*
FROM
(
    select *
    from
    (
            select T.ID, T.SCORE, T.TYPE 
            from YourTable T
            WHERE TYPE='A'
            ORDER BY T.SCORE DESC

    )
    WHERE ROWNUM <=2
    UNION 
    select *
    from
    (
            select T.ID, T.SCORE, T.TYPE 
            from YourTable T
            WHERE TYPE='B'
            ORDER BY T.SCORE DESC

    )

    UNION 
    select *
    from
    (
            select T.ID, T.SCORE, T.TYPE 
            from YourTable T
            WHERE TYPE='C'
            ORDER BY T.SCORE DESC

    )


) T
ORDER BY T.SCORE DESC;

edited Jan 04 '13 at 18:07

answered Jan 04 '13 at 17:54

A B

4,068
1
20
23

Not quite. I would like this ordering to occur for every 10 rows. And I would also like to have a minimum of 2 Type C in every 10 rows. – rami Jan 04 '13 at 17:58
Can you explain what you mean by ordering "within every 10 rows"? – A B Jan 04 '13 at 18:01
That means that the first 10 records would have at least 2 typeC, the second 10 records would have at least 2 typeC, and so on. – rami Jan 04 '13 at 18:03
It depends - do you still want ordering to be by SCORE (descending)? If you want the descending order on SCORE, that may take precedence over the at-least-2-C-per-10 and at-most-2-A-per-10 ordering requirements. You can still use PHP to manually order the records (post processing) – A B Jan 04 '13 at 18:16

Saharsh Shah · Answer 2 · 2013-01-05T05:23:26.967

0

Try this:

SELECT id, score, a.type 
FROM (SELECT id, score, a.type, IF(@lasttype = (@lasttype:=a.type), @index:=@index+1, @index:=0) indx 
      FROM tablename t, (SELECT @lasttype:='', @index:=0) AS a 
      ORDER BY TYPE, score DESC) AS a 
WHERE (a.type NOT IN ('A', 'C') OR (a.type IN ('A', 'C') AND indx <= 2)) 
ORDER BY score DESC;

edited Jan 05 '13 at 05:23

answered Jan 04 '13 at 18:19

Saharsh Shah

28,687
8
48
83

Restrictions on SQL order

2 Answers2