Query Help need with sql Sorting

Question

I have a table with roll_no & No_of_items (Both not primary)

Is it possible to order them the following way: The first entry is with the highest no of items, then all the entries belonging to that roll_no come, then the next entry from a diff roll_no that has the highest no of items and then all the entries belonging to that client and so on

For Eg.

Column 1 Column 2
A          12
A          05
C          19
C          18
C          02 
B          05

Should display as:

C 19
C 18
C 02
A 12
A 05
B 05

are those contained in the same column? or different columns? — Taryn
– Taryn, Commented Oct 17, 2012 at 20:58

podiluska · Accepted Answer · 2012-10-17 21:22:39Z

1

Try this.

select t1.*
from yourtable t1
    inner join 
    (
        select 
            client_id, max(numberofdays) cnt 
        from yourtable 
        group by client_id
    ) t2
        on t1.client_id = t2.client_id
order by cnt desc, client_id, numberofdays desc;

This may also work, dependant on SQL flavour

select *
from yourtable
order by 
    max(numberofdays) over (partition by client_id) desc, 
    client_id, 
    numberofdays desc;

Edit: added client_id to order to keep rows with same number.

edited Oct 17, 2012 at 21:22

answered Oct 17, 2012 at 21:00

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ibonly · Accepted Answer · 2012-10-17 21:03:09Z

1

what you will use in your sql query is 'order by' your code will look like this using MySql

<?php
$query = mysql_query("select `client_id`, `No_of_Days` FROM `tablename` order by `No_of_Days` DESC ");
while($row = mysql_fetch_object($query))
{
     echo $row->client_id." ".$row->No_of_Days;
}
?>

the query is what does the main work here

answered Oct 17, 2012 at 21:03

ibonly

1331 gold badge3 silver badges10 bronze badges

1 Comment

podiluska Over a year ago

This doesn't return the order requested.

Collectives™ on Stack Overflow

Query Help need with sql Sorting

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related