Friday, January 20, 2017

T-SQL get one records from multiple same record based on max value for join

Let's say you have same type 6 record, into this record when you will picked data you will pick only one record which has highest value.

 SELECT *   
 FROM rx r  
 INNER join   
  (SELECT distinct t1.*  
 FROM profile t1  
  LEFT OUTER JOIN profile t2  
   ON (t1.rxno_FK = t2.rxno_FK AND t1.nrefillno < t2.nrefillno )  
 WHERE t2.dispid_PK IS NULL) AS p ON r.[rxno_PK] = p.rxno_FK  

SQL Get all Index create script from Database

To get all script from database as a create new index into another database you can use the following --Get all Index Script SELECT...