current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

I am working on a postgresql query that i am not sure how to produce the output.

Lets say i have a sql query whose output i want is 

         name    date     visit_number  visit
          x    2011-01-01     123         ??   (value i want=1)
          y    2011-01-01     123         ??   (value i want=empty)
          a    2011-02-02     345         ??   (value i want=1) 
          b    2011-02-02     345         ??    (empty)
          c    2011-02-02     345         ??     (empty)

currently my sql query contains all the values except the last column visit. I want the visit column to work this way...if visit_number contains same value for multiple rows, i want the column visit to show the value 1 for the first row and just null or empty for the remaining rows where the visit_number is the same. How do i do that???

i could write the sample query in any way.it could simply be :

    select name,date,visit_number from sometable order by date;

I am using postgres 8.1 version.

Thanks

share|improve this question
2  
Well, what is your current sql query? –  Mr E Feb 27 '12 at 15:58
    
Mr E..you could write it anyway....for example simply select name,date,visit_number from sometable; i would like to add this new column visit whose result should be what i described above –  cableload Feb 27 '12 at 15:58
    
It would be very helpful if you post your current query! –  bluefeet♦ Feb 27 '12 at 15:58
1  
We need to see your table structure and your current query in order to help you. –  Daniel Lyons Feb 27 '12 at 16:02
    
Please let me know if you need further information that what is provided. Thanks –  cableload Feb 27 '12 at 16:09

2 Answers 2

up vote 0 down vote accepted

The first thing you should do is upgrade to a modern day version of PostgreSQL. Version 8.1 has reached end of life in November 2010.

In a more recent version you can conveniently solve this with window functions:

SELECT name, date, visit_number
     , CASE WHEN row_number() OVER (PARTITION BY visit_number
                                    ORDER BY date, name) = 1
          THEN 1
          ELSE NULL
       END AS visit
FROM   tbl
ORDER  BY date, name;

I ordered by name additionally to break ties.

For versions before PostgreSQL 8.4, this query should work (untested):

SELECT name, date, visit_number
     , CASE WHEN EXISTS (
         SELECT *
         FROM   tbl t1
         WHERE  t1.visit_number =  tbl.visit_number -- more to make it unique?
         AND    t1.date <= tbl.date -- or more columns to make order unambiguous
         AND    t1.name <  tbl.name
         )
       THEN NULL ELSE 1 END AS visit
FROM   tbl
ORDER  BY date, name;
share|improve this answer
    
I understand. I am an oracle guy and that was the first thought that came to my mind (using partition/window). Infact i even tried to run the query that way and then realized that the version does not support. Unfortunately right now, i cant do anything about upgrade as its not in my hands. –  cableload Feb 27 '12 at 16:17
    
@cableload: I added a "vintage" solution for the desperate souls that are stuck with ancient versions. ;) –  Erwin Brandstetter Feb 27 '12 at 16:30
    
Brandstetter Yes..great –  cableload Feb 27 '12 at 16:48
up vote 0 down vote

This is the query:

select *, 
  case when row_number() over (partition by visit_number) = 1
    then 1
    else null
  end
from t

Here is an example

Edit:

Without window function:

select t4.*, case when t3.name is not null then 1 end as visit from t t4
left join (
  select t1.* from t t1
  left join t t2 on t1.name > t2.name and t1.date = t2.date and 
    t1.visit_number = t2.visit_number
  where t2.name is null
) as t3
on t3.name = t4.name and t3.date = t4.date and t3.visit_number = t4.visit_number

Here is an example

NOTE: If name is a key then the last comparison t3.date = t4.date and t3.visit_number = t4.visit_number can be removed

share|improve this answer
    
thanks..its perfect...!!! –  cableload Feb 27 '12 at 16:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.