CODEWITHSUNDEEP
sql
Vishnu Priyan Rangasamy
Vishnu Priyan Rangasamy

Reputation: 195

Select rows with max value of a column

I have a table like the following:

acct_id----+---Bill_Id-------+--Bill_dt-----+---alt_bill_id--
12345          123451           02-JAN-2014     101
12345          123452           02-JAN-2014     102 
12346          123461           02-JAN-2014     103
12347          123471           02-JAN-2014     104

I need to fetch the data ignoring the least alt_bill_id if there are two rows for same acct_id. In this case I need to ignore the row for acct_id 12345 and alt_bill_id 101. I need a result like the following:

 acct_id----+---Bill_Id-------+--Bill_dt-----+---alt_bill_id--
    12345          123452           02-JAN-2014     102 
    12346          123461           02-JAN-2014     103
    12347          123471           02-JAN-2014     104

Upvotes: 1

Views: 102

Answers (2)

Vishnu Priyan Rangasamy
Vishnu Priyan Rangasamy

Reputation: 195

I have used the query like this:

SELECT T1.* FROM CI_BILL T1
WHERE ALT_BILL_ID IN (SELECT MAX(ALT_BILL_ID)
FROM CI_BILL T2 GROUP BY T2.ACCT_ID);

Upvotes: 0

Damien Black
Damien Black

Reputation: 5647

You have to use a sub-query to find the highest value(bill_id), then join to that sub-query. Like this:

SELECT main.* FROM my_table AS main
  JOIN (
        SELECT MAX(bill_id) AS bill_id
         FROM my_table 
     GROUP BY acct_id
        ) AS highest
  ON highest.bill_id = main.bill_id;

And here is the SQLFiddle for anyone who wishes to try it out: http://sqlfiddle.com/#!2/fc66a/2

Upvotes: 5

Related Questions