Distinct on single column?

Hi,

This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.

SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d

INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p ON p.code = sol.part

WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0

ORDER BY (p.code), datum DESC

The problem with this is that it returns multiple lines for every product (p.code). Like so:

code description price quantity total date

603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00

603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00

603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00

603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00

603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00

603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00

603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00

I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved

edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.

Hello

Just a few ideas, do not have an MSSQL instace nearby to test:

1) use cursor, which I'd prefer to avoid

- declare cursor for "Select Distinct (code) From EfasLive..part"

- for each cursor value do the select on joined tables where date = MAx(date) to get the most recent value

2) do something like

Select ....
From (Select Distinct (code) From EfasLive..part) as p Inner Join... (the rest of the tables)...
Where date = Max(date)

The idea is to join distinct "code" values with other tables and filter only the most recent one for each table (that's what "Max(date)" is for)

3) try to use CTEs (Common Table Expressions)

Post the solution after you find one! Tnx
|||

Hmz I'll try out some of this stuff. Thx! But the multiple instances of code don't come from Efaslive..part. They are actually from so_line. An so_line is actually an orderline. For every order there could be multiple lines each containing a different product. But since it's over a timespan of 3 months it will include multiple orders and thus multiple so_lines containing the same product (once for every order it was in). So doing a distinct on code in Efaslive..part probably won't work. Or at least it doesn't make sense to me I'll most definitely look into CTEs and post my findings or a solution.

edit: actually this can be simplified ... just pretend that the result I get is a simple select query from a single table. As if it was a CTE Even then I'd have no clue how to drop the older records The only technique I know is to group them but then you'd have to use MAX or COUNT or AVG or whatever .. and then I wouldn't have the correct price and/or date. So you wouldn't realy be dropping them.

I'll look into the pointer thing.

|||

Here the query,

Code Snippet

;With CTE

as

(

SELECT DISTINCT

p.code

, p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description

, sol.p_sales as price

, sol.q_ordered as quantity

, sol.p_sales * sol.q_ordered as total

, so.date_in as date

, max(so.date_in) over(partition by p.code) as maxdate

--, Row_Number() over(partition by p.code order by so.date_in desc) rid

FROM

EfasLive..debtor AS d

INNER JOIN Informatica..so AS so

ON so.deb_code = d.code

AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol

ON sol.code = so.code

AND sol.co_code = so.co_code

AND sol.acc_year = so.acc_year

AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p

ON p.code = sol.part

WHERE

d.[grp{003}] = 'GROUP'

AND p.co_code = 1

AND p.code NOT LIKE '&%'

AND so.date_in > DATEADD(m,-3,GETDATE())

AND sol.q_ordered > 0

)

Select

code

, description

, price

, quantity

, total

, date

From

CTE

Where

date = maxdate

--rid=1