Postgresql: Choose between two similar rows based on a boolean column

Question

I have a table that is a list of every item in the system. There is a column that says whether the item is a default item or not. In the query I'm going through the items and selecting ones that have a MSDS associated with them, however my query returns multiple items for the same item number because multiple records exist for the item.

So what I want to do is limit the results to just default items. Normally I would just do WHERE itemsrc.itemsrc_default, however not every item has been setup with a default record, so what I want to do is go through the table and if a default record exists for an item than give priority to that item, otherwise if no default record exists, return the first record for that item number.

I'm not sure if I would do this as a subquery in the WHERE clause of the main query or if I should make function that checks for defaults.

GarethD · Accepted Answer · 2013-12-31 14:50:09Z

2

You could use DISTINCT ON

SELECT  DISTINCT ON (ItemID) ItemID, itemsrc_default, Column1, Column2 
FROM    itemsrc
ORDER BY ItemID, itemsrc_default DESC, Column1, Column2;

From the docs:

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

Simple Demo on SQL Fiddle

You can also get the same results using ROW_NUMBER()

SELECT  *
FROM    (   SELECT  ...,
                    ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY itemsrc_default DESC, SomeCol) AS RowNum
            FROM    itemsrc
        ) i
WHERE   RowNum = 1;

Example using ROW_NUMBER

edited Dec 31, 2013 at 14:50

answered Dec 31, 2013 at 14:40

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wildplasser · Accepted Answer · 2013-12-31 15:29:31Z

0

SELECT *
FROM itemsrc src
WHERE itemsrc_default = True
OR NOT EXISTS (
    SELECT * FROM itemsrc nx
    WHERE nx.itemId = src.itemID
    AND nx.key_column < src.key_column
    );

answered Dec 31, 2013 at 15:29

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Collectives™ on Stack Overflow

Postgresql: Choose between two similar rows based on a boolean column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related