compare fields from a row with another row from the same table

[henky]

Hello....

In some (sql)languages there is a possibility to compare a field from row 1 with the same field in row 2.

P.E.
here is the query:

SELECT ref, cust, purch_date
FROM sell
ORDER BY ref, cust, purch_date
GO

ref cust purch_date
123 1001 2005-01-03
123 1001 2005-04-01
123 1004 2005-02-01
172 1043 2005-02-28
172 1043 2005-03-20
172 1043 2005-03-31
195 1047 2005-01-13

What I'd like to have is:

SELECT ref, cust, purch_date, ref_from_prev_row, cust_from_prev_row
FROM sell
ORDER BY ref, cust, purch_date
GO

the output:
ref|cust|purch_date|ref_from_prev_row|cust_from_prev_row
123 1001 2005-01-03 Null Null
123 1001 2005-04-01 123 1001
123 1004 2005-02-01 123 1001
172 1043 2005-02-28 123 1004
172 1043 2005-03-20 172 1043
172 1043 2005-03-31 172 1043
195 1047 2005-01-13 172 1043

maybe there is an function like prior or next.

select ref, prior(ref) as ref_from_prev_row from sell ....

Can you help me?

Henky

 

[JamesLean]

In order to do this you have to have some column you can use to identify the logically "previous" row - data is not stored in any particular order internally.

Given the sample data you provided there doesn't appear to be a column available for you to do this. Therefore you would have to create a temp table which duplicated your main table, add an identity row and insert the data into it. You could then use the identity value to work out which was the previous row.

--James

 

[vongrunt]

From sample data it appears that (ref, cust, purch_date) are sorted and unique. Is that always true?


------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[henky]

The reason I want to do this is because I have to select each record where the CUST is unique. I want to select the whole last row when I use this

ORDER BY ref, cust, purch_date

Maybe there is anther way to select the whole last row for each unique ref and cust with the highest purch_date.

Out of this total result:
ref cust purch_date
123 1001 2005-01-03
123 1001 2005-04-01
123 1004 2005-02-01
172 1043 2005-02-28
172 1043 2005-03-20
172 1043 2005-03-31
195 1047 2005-01-13

I need the following
ref cust purch_date
123 1001 2005-04-01
123 1004 2005-02-01
172 1043 2005-03-31
195 1047 2005-01-13

 

[henky]

@vongrunt

From sample data it appears that (ref, cust, purch_date) are sorted and unique. Is that always true?

------

Yep. those 3 fields always are unique

 

[JamesLean]

select the whole last row for each unique ref and cust with the highest purch_date

SELECT ref, cust, MAX(purch_date)
FROM table
GROUP BY ref, cust
ORDER BY ref, cust

If there are more columns that you need to select as well then use:

SELECT ref, cust, purch_date, other_cols
FROM table t1
WHERE purch_date = (
    SELECT MAX(purch_date) FROM table
    WHERE ref = t1.ref
      AND cust = t1.cust
  )
ORDER BY ref, cust

--James

 

[henky]

Thanks for the support I'll give it a try...