CODEWITHSUNDEEP
sqloracle10g
maulzey
maulzey

Reputation: 4240

Query Help need with sql Sorting

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

Upvotes: 1

Views: 56

Answers (2)

podiluska
podiluska

Reputation: 51494

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.

Upvotes: 1

ibonly
ibonly

Reputation: 133

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

Upvotes: 1

Related Questions