Hi All,
 
Once again back to square to learn fundamentals of sql.
 
I came accross the situation where I have a table called Book_Details.
 
This is dummy example I am taking here the table structure looks like:
 
Book_NO  --------- int
Book_ID  --------- int
Group_Code --------- varchar
Main_Brach --------- varchar
Sub_Branch --------- varchar
Publish_Date --------- datetime
Original_Price --------- float
Selling_Price --------- float

 
Book  Book  Group     Main      Sub      Publish           Original   Selling
NO     ID      Code      Branch  Branch  Date              Price       Price
  
3        23      'AA02'    'KAR'     'KAR'    '2011-03-01'   13.25      13.25      
5        33      'AWAP'   'KAR'     'KAR' '2011-05-18'   13.45      13.45
5        55      'AWAP'   'KAR'     'KAR' '2011-05-18'   12.65      10.65      
4        57      'AA02'    'KAR'    'KAR' '2011-05-18'   11.12      11.12
4        59      'AA02'    'KAR'     'KAR' '2011-05-20'   13.75      13.75    
5        65      'AWAP'   'KAR'     'KAR' '2011-05-23'   13.25      13.25
6        77      '14128'   'KAR'   'KAR' '2011-05-23'   10.65      13.65     
5        333    'AWAP'   'KEL'   'KEL' '2011-05-18'   13.45      13.45
5        355    'AWAP'   'KEL'   'KEL' '2011-05-18'   9.65          5.65
4        357    'AA02'    'KEL'   'KEL' '2011-05-18'   11.125    11.12
 

Here I would like to get the result as, for Each Date, for Each Main Branch and for Each Sub Branch, look for Maximum value of "Book_NO" column,
if there are some, same number of values exist for "Book_No" column for any respective date, then, look for Maximum value of the "Book_ID" column,
for the same "Book_NO" value, and for Maximum Value of the "Book_ID", take the "Original_Price" and "Selling_Price" column values.
 
To make it clear just take above example's 4 records which has following values:
 
Book  Book  Group     Main      Sub      Publish           Original   Selling
NO     ID      Code      Branch  Branch  Date              Price       Price   
 
5        33      'AWAP'   'KAR'     'KAR' '2011-05-18'   13.45      13.45
5        55      'AWAP'   'KAR'     'KAR' '2011-05-18'   12.65      10.65
4        57      'AA02'    'KAR'    'KAR' '2011-05-18'   11.12      11.12      
5        333    'AWAP'   'KEL'   'KEL' '2011-05-18'   13.45      13.45
5        355    'AWAP'   'KEL'   'KEL' '2011-05-18'   9.65          5.65
4        357    'AA02'    'KEL'   'KEL' '2011-05-18'   11.125    11.12
 

In above case, we would look for the date '2011-05-18' which has 4 records in the database, out of which 2 for 'KAR' branches and 2 for 'KEL' branches.
 
 
Now for the "Publish_Date" -> '2011-05-18', for "Main_Branch" -> 'KAR' and for "Sub_Branch" -> 'KAR', we should look for Maximum value of "Book_NO"
column i.e. it should be -> 5 in above case, now we have two records for the "Book_No" column value 5, so now we should look for the Maximum value of the
"Book_ID" column, so we should get the value -> 55, and now for this record's "Original_Price" and "Selling_Price" we need to take, so it should be 12.65 and
10.65 respectively.
 

So the entire sql should give following columns and the values:
 

Book  Book  Group     Main      Sub      Publish           Original   Selling
NO     ID      Code      Branch  Branch  Date              Price       Price   
 
3        23      'AA02'    'KAR'     'KAR'    '2011-03-01'   13.25      13.25 
5        55      'AWAP'   'KAR'     'KAR' '2011-05-18'   12.65      10.65
5        355    'AWAP'   'KEL'   'KEL' '2011-05-18'   9.65          5.65
4        59      'AA02'    'KAR'     'KAR' '2011-05-20'   13.75      13.75
6        77      '14128'   'KAR'   'KAR' '2011-05-23'   10.65      13.65
 

I hope I might have succeded to explain the entire requirement.
 
I would like to learn how to write a standard sql to achieve above result.

A Heartful Thank You In Advance.
 
 
With Best Regards.
 
Vishal

[Non-text portions of this message have been removed]