CODEWITHSUNDEEP
sqlsql-server
SHAKIR SHABBIR
SHAKIR SHABBIR

Reputation: 1295

Filteration of Rows based on a column value

I have a table called AssetTable which stores AssetId

I have another table called AssetBookTable which stores two fields BookId and CurrentOperationsTax

Another table AssetBook that stores AssetId and BookId as foriegn keys.

Now, I have a situation in which I have to filter records based on AssetBookTable.CurrentOperationsTax

This, is what I am getting now, This is so far I have achieved

And this, is what I want: This is what I want to achieve

**

The logic is that I want only one BookId per AssetId that has either Current/Operation/Tax for CurrentOperationsTax field.

**

Here's the SQL fiddle written so far:

SQLFiddle

Any help is greatly appreciated.

Upvotes: 2

Views: 105

Answers (2)

db_brad
db_brad

Reputation: 923

I prefer using the ROW_NUMBER approach. It does require the use of a subquery. In summary it is grouping the records by the CuurentOperationsTax number and ordering by book id and giving the row number for each grouping. Then in the outer select I filter out how many I want for each grouping. In this example just 1.

SELECT   AssetId
        ,BookId
        ,CuurentOperationsTax
        ,RowNum
FROM   (
        SELECT
           ab.AssetId
          ,ab.BookId
          ,abt.CuurentOperationsTax
          ,ROW_NUMBER() OVER(PARTITION BY abt.CuurentOperationsTax ORDER BY ab.BookId) AS RowNum
        FROM
         AssetBook ab
        JOIN AssetTable AT ON AT.AssetId = ab.AssetId
        JOIN AssetBookTable abt ON abt.BookId = ab.BookId
      ) AS b
WHERE b.RowNum = 1

Upvotes: 1

dognose
dognose

Reputation: 20909

You can simple remove the GROUP BY BookID. But of course you then need to aggregate the BookID some how. Here using the MIN() function:

SELECT
   ab.AssetId
  ,MIN(ab.BookId) as BookID
  ,abt.CuurentOperationsTax
FROM
 AssetBook ab
JOIN AssetTable at ON at.AssetId = ab.AssetId
JOIN AssetBookTable abt ON abt.BookId = ab.BookId
GROUP BY 
   ab.AssetId
  ,abt.CuurentOperationsTax

http://sqlfiddle.com/#!6/e3477/42

Upvotes: 1

Related Questions