1

I'm having a little problem with one query that I'm writing. I've a lot of joins, and a lot of columns that I extract, in the where clause I compare date column with the minimum value of the same table. But when I got a same date for two rows, I need to get only one row. The where clause is like that:

bt.da2 = (select min(btreg.da2) from bt btreg.....

the query results a lot of customers, every customer has that bt.da2 date. I need when one customer has two rows, with the same value of the bt.da2 , I need to take only one of the two rows, not the two. I may not explained myself clear. Please if anyone have a little clue what I'm asking, and something is not clear, please ask me.

I'm using PostgreSQL 8.3

Regards, Julian

Improve this question
3
  • 2
    You will need to provide more detail: the full statement you are using and possibly the create table statements for the tables involved. Ideally a SQLFiddle example that shows your current problem Commented Feb 26, 2013 at 11:51
  • I'll be happy to show it to you, there is no problem, but it involves 32 tables, I'cant show you the create table statements of all. Basically there is a products table, a customers table, a payments table, and bank transfer tables (the bt table in the example). I just take the products table, left join the bank transfer table, and because some customers have more than one bank transfers that are at a same date, that is why it shows me two or more rows, that thing I want to avoid. Is there a way to "say": if there are two transfers with same dates, just return one of them, doesn't matter who. Commented Feb 26, 2013 at 12:44
  • Do your tables have at least one unique key column (or a combination of columns) NOTE: you should at least add the definition of one of the tables to the question. NOTE 2: upgrade. Commented Feb 27, 2013 at 13:23

2 Answers 2

Reset to default
2

It's hard to tell with so little information, but I would try something like this:

select *
from (
  select product_id, -- assumed to be the primary key 
         ...
         row_number() over (partition by product_id order by bt.da2) as rn
  from products pr
    left join bt on bt.da2 = pr.some_col
) t
where rn = 1

the row_number() function is used to create consecutive numbers for each product. The outer where clause then picks the first one. You can change the order by in the window definition to influence which one you pick.

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

6 Comments

Thanks for the answer, but Postgresql 8.4 do not support the function row_number() :(
@dna: yes it does. 8.4 was the first version where this was introduced.
my mistake, the server version is 8.3.13 :(
@dna: then you should update as soon as possible. 8.3 is no longer supported (and is quite old anyway as is 8.4)
thanks, but this is a decision that my bosses make, I've no power over there, and I'm seeking for solutions, twisting and sh*t to make the results they want from me :) ... with 8.3.13
|
0

You should be able to sort out duplicate values of da2 using:

bt.da2 = (select distinct min(btreg.da2) from bt btreg.....

I tried this out using PostgreSQL 9.1, but I am sure the distinct keyword will work as expected in 8.4 as well.

Improve this answer

2 Comments

The way the condition is structured, the sub-select has to return only a single row, so the distinct won't make a difference.
the min() function returns only one date. The problem is, when it begins to compare this date to the outer bt table, where I've two rows for a customer with the same date as the subquery returns

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.