get rid of a field in group by

I need to sort my record by adult_rate, from cheapest to expensive.

this is my sql:


From the above sql, i got result like this:


From the result listing, u can see that Class is being grouped together, then only do the sorting of rates. But what i want is sort the rate first no matter what, this is the result i want:


the src_code is the class (m,L), i tried to get rid of the src_code in the group by, but when i take it out, it generate sql error. what can i do? pls advise.

If I understand your question perfectly, ORDER BY clause in your query suppose to work. You are using the column name in ORDER BY clause, now try to use the column position of this particular column in OREDR BY clause

For example according to your query, you clause would be:

ORDER BY 11
/

But this is not guarnteed to be work. I am unable to run your query without table staructure and data.

The group by is going to superceed you order by. I would make it an inline view then put your order by on the outside.

Something like

SELECT col1, col2
FROM (SELECT X col1,
MAX(Y) col2
FROM TABLE
GROUP BY X)
ORDER BY col1

HI Shafique, i tried your method, result displayed is the same, it did not sort by rate.

gcisjxs0, i wanted to try ur method but i do not know how to separate my query...

Based on the query you have in the thread it would be something like this:

SELECT
trcodetypeno,
cvm_dip_id,
DATABASE,
productcode,
productno,
ratecodeno,
ratetypeno,
src_code,
srt_code,
operator_com_code,
adult_rate,
maxcurcode,
minbsadltrt,
maxchildrate,
minbsdscinfrt,
prp_rnt_id,
cvm_id
FROM
( SELECT
ratecodeno||ratetypeno trcodetypeno,
cvm_dip_id,
DATABASE,
productcode,
productno,
ratecodeno,
ratetypeno,
src_code,
srt_code,
operator_com_code,
adult_rate,
MAX(currencycode) maxcurcode,
MIN(base_discount_adult_rate) minbsadltrt,
MAX(child_rate) maxchildrate,
MIN(base_discount_infant_rate) minbsdscinfrt,
prp_rnt_id,
cvm_id
FROM twn_rate r1
WHERE ROWNUM<1000000
AND suppliercode != 'TWN'
AND (prd_dip_id=0 OR prd_dip_id IN (70154, 70157, 70155, 70156, 70159, 70174, 70211, 70212, 70268, 70213, 70215, 70216, 70217, 70229, 70281, 70267, 70272, 70153))
AND src_code IN ('B','H','L','M','N','Q','T','V','X','S','W','Y')
AND srt_code LIKE '%RETURN'
AND productno IN (SELECT productno
FROM twn_product_location l
WHERE location_type='Origin'
AND townno = 42
AND l.DATABASE = r1.DATABASE)
AND productno IN (SELECT productno
FROM twn_product_location l
WHERE location_type='Destination'
AND townno = 460
AND l.DATABASE = r1.DATABASE)
AND ('15-SEP-2004' <= todate
AND '22-SEP-2004' >= fromdate)
AND (!cvm_rtc_id = 0 OR cvm_rtc_id IN ( SELECT rbp_rtc_id
FROM twn_rate_basis_period rb
WHERE cvm_rtc_id = rbp_rtc_id
AND rb.DATABASE=r1.DATABASE
AND ('15-SEP-2004' <= rbp_to_date
AND '22-SEP-2004' >= rbp_from_date) ))
GROUP BY
adult_rate,
DATABASE,
productcode,
productno,
srt_code,
cvm_dip_id,
prp_rnt_id,
cvm_id
)
ORDER BY adult_rate ASC