0

I have a table with these three attributes, say:

ItemID varchar(20),
RetailerID int,
ItemMfd_Date DATE

Now I want all those records such that if there are multiple retailers for a given Item + ItemMfd_Date we need to pick the row that is with minimum RetailerID.

Suppose we have two attribute additional, and we want one more attribute as result but condition is those records must have attr2 = 1

mat7
  • 297
  • 3
  • 11
  • 1
    You are always welcome to edit your question to *clarify* and *add missing information*. But please do not change the nature of the question after answers have been given. Start a new question for that. – Erwin Brandstetter Aug 21 '15 at 15:05

2 Answers2

0

Use the min aggregate function and group by the two other columns:

select ItemID, min(RetailerID) as min_RetailerID, ItemMfd_Date 
from table 
group by ItemID, ItemMfd_Date;
jpw
  • 44,361
  • 6
  • 66
  • 86
  • This is wrong , this give multiple rows for same ItemID, ItemMfd_Date – mat7 Aug 21 '15 at 14:10
  • No, it doesn't unless `ItemMfd_Date` isn't a `date` but a `timestamp` value. Can you show some sample data where it gives multiple rows with the same ItemID and ItemMfd_Date? – jpw Aug 21 '15 at 14:14
  • @mat7: The query seems correct. After `group by ItemID, ItemMfd_Date`, there should be only *one*. There must be a misunderstanding. – Erwin Brandstetter Aug 21 '15 at 14:18
  • @jpw What if suppose we have two more attributes, attr1,attr2 and we want only attr1 also with attr2 value as 1 – mat7 Aug 21 '15 at 14:26
  • @mat7 That is not the scenario the question described and would indeed need a different solution (like the ones suggested by Erwin). My query works for the scenario you described in the question. – jpw Aug 21 '15 at 14:28
0

DISTINCT ON is simplest to retrieve the one with minimum RetailerID row per (ItemID, ItemMfd_Date):

SELECT DISTINCT ON (ItemID, ItemMfd_Date) *
FROM   tbl
ORDER  BY ItemID, ItemMfd_Date, RetailerID;

Details:

For distributions with many duplicates, other query styles may be faster:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Is DISTINCT ON not supported by postgres SQL ? – mat7 Aug 21 '15 at 14:24
  • @mat7: `DISTINCT ON` has been in Postgres *for ever*. Follow the links for details. What do you get for `SELECT version()`. Please update your question with your Postgres version and the exact table definition if the problem persists. – Erwin Brandstetter Aug 21 '15 at 15:02