Distinct/filter over several columns???? How? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distinct/filter over several columns???? How?

Author

Topic

rtr1900
Starting Member

48 Posts

Posted - 2008-01-16 : 07:02:11

Hi,

I made a select (with inner joins) over several tables. Which returns me a lot of records. now what i am trying to do is to reduce the lines to the last date of it. excample

Product NAME DATE Seller
1234 Icecream 12/01/2007 David
1234 Icecream 12/03/2007 John
1234 Icecream 08/01/2008 David
1234 Icecream 11/01/2008 David
1234 Icecream 12/01/2008 John
1234 Icecream 12/01/2008 David

It should return only (each record of the same seller with the latest date)

Product NAME DATE Seller
1234 Icecream 12/01/2008 John
1234 Icecream 12/01/2008 David

Which are the last dates we have ordered something from them

But when I add a Distinct, it doens´t work well.

My orginal code is

SELECT ART.COD_ART, ART.DESCRIP, lped.FEC_STOCK, lped.FECHA, lped.DTO, ped.COD_PED, ped.COD_PRO,
farm.name AS name
FROM lped INNER JOIN
ART ON lped.COD_ART = ART.COD_ART INNER JOIN
ped ON lped.COD_PED = ped.COD_PED INNER JOIN
farm ON ped.COD_PRO = farm.cod_farm
ORDER BY ART.COD_ART, lped.FECHA

Any help is welcome..... Thx

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-16 : 07:14:20

Look in BOL for MAX and GROUP BY.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rtr1900
Starting Member

48 Posts

Posted - 2008-01-16 : 07:36:20

I tried that, but it just gives me too much records back. i only need to return the latest record.
As it returns also some other columns (see my SQL) which are unique, or repeated. But I need only filter on the name and the date.
But as I use GROUP, I need to add all the columns.

I need a break :-)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 07:45:46

quote:
Originally posted by rtr1900

I tried that, but it just gives me too much records back. i only need to return the latest record.
As it returns also some other columns (see my SQL) which are unique, or repeated. But I need only filter on the name and the date.
But as I use GROUP, I need to add all the columns.

I need a break :-)

Can you post the query you used?

Madhivanan

Failing to plan is Planning to fail

rtr1900
Starting Member

48 Posts

Posted - 2008-01-16 : 08:02:39

Here its is:

SELECT ART.COD_ART, ART.DESCRIP, lped.FEC_STOCK, lped.FECHA, lped.DTO, ped.COD_PED, ped.COD_PRO,
farm.name AS name
FROM lped INNER JOIN
ART ON lped.COD_ART = ART.COD_ART INNER JOIN
ped ON lped.COD_PED = ped.COD_PED INNER JOIN
farm ON ped.COD_PRO = farm.cod_farm
ORDER BY ART.COD_ART, lped.FECHA

If you need to see the results and what I want to achieve, tell me and I will post it
Thx already

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 08:21:22

quote:
Originally posted by rtr1900

Here its is:

SELECT ART.COD_ART, ART.DESCRIP, lped.FEC_STOCK, lped.FECHA, lped.DTO, ped.COD_PED, ped.COD_PRO,
farm.name AS name
FROM lped INNER JOIN
ART ON lped.COD_ART = ART.COD_ART INNER JOIN
ped ON lped.COD_PED = ped.COD_PED INNER JOIN
farm ON ped.COD_PRO = farm.cod_farm
ORDER BY ART.COD_ART, lped.FECHA

If you need to see the results and what I want to achieve, tell me and I will post it
Thx already

Where are Product, NAME, DATE and Seller columns?

Madhivanan

Failing to plan is Planning to fail

rtr1900
Starting Member

48 Posts

Posted - 2008-01-16 : 08:56:33

There aren´t there. I just made them up, to explain my question.

In my original code, I need to get only the latest DATE (FECHA in my code)and the seller.(NAME in my code)
I explain, there are dates (FECHA)when we buy stuff from different sellers (NAME), and every day there are different conditions of discounts (DTO in my code).
But I only need to get, from every product:
1) Every seller who has ever sold me the product
2) Only of the last DATE they offered/sold it.

So this would give me 1 line of each product for each seller. And with my code, I get all the dates + sellers of each product. So I just need to get it work that it will only give me the latest date. i tried to add MAX(), but no result....

Thx already Madhivanan!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 09:17:22

To give you an idea, start with

Select Product, NAME, max(DATE) as DATE, Seller from table
group by Product, NAME ,Seller

Madhivanan

Failing to plan is Planning to fail

rtr1900
Starting Member

48 Posts

Posted - 2008-01-16 : 10:41:14

No Luck, I tried this:

SELECT MAE_ART.COD_ART AS ART, MAE_ART.DESCRIP AS DESCRIPCION, farm.nombre AS NOMBRE, mae_ped.COD_PED AS PEDIDO,
MAX(mae_lped.FECHA) AS FECHA
FROM mae_lped INNER JOIN
MAE_ART ON mae_lped.COD_ART = MAE_ART.COD_ART INNER JOIN
mae_ped ON mae_lped.COD_PED = mae_ped.COD_PED INNER JOIN
farm ON mae_ped.COD_PRO = farm.cod_farm
GROUP BY MAE_ART.COD_ART, MAE_ART.DESCRIP, farm.nombre, mae_ped.COD_PED
ORDER BY MAE_ART.COD_ART

The result of the table (a part of it) is:

ART Descripcion NOMBRE PEDIDO FECHA
150805 ICECREAM DAVID 1234 26/03/2007
150805 ICECREAM JOHN 1235 22/06/2007
150805 ICECREAM JOHN 31235 06/11/2007
150805 ICECREAM JIMMY 12534 27/07/2007
150805 ICECREAM JIMMY 12554 31/12/2007
150805 ICECREAM DAVID 1234 26/12/2007

While The result should have been:

ART Descripcion NOMBRE PEDIDO FECHA
150805 ICECREAM JOHN 31235 06/11/2007
150805 ICECREAM JIMMY 12554 31/12/2007
150805 ICECREAM DAVID 1234 26/12/2007

As you see, it should only return the record with the latest date (of the order)

Thx again!

rtr1900
Starting Member

48 Posts

Posted - 2008-01-16 : 11:51:31

I believe that I have it:
SELECT mae_lped.COD_ART, MAE_ART.DESCRIP, farm.nombre, farm.cod_farm, MAX(mae_lped.FECHA) AS Expr1, MAE_ART.COD_ART AS Expr2,
MAX(mae_lped.DTO) AS XXX
FROM MAE_ART INNER JOIN
mae_lped ON MAE_ART.COD_ART = mae_lped.COD_ART INNER JOIN
mae_ped ON mae_lped.COD_PED = mae_ped.COD_PED INNER JOIN
farm ON mae_ped.COD_PRO = farm.cod_farm
GROUP BY mae_lped.COD_ART, MAE_ART.DESCRIP, farm.nombre, farm.cod_farm, MAE_ART.COD_ART
ORDER BY MAE_ART.COD_ART

The problemas start is I change "MAX(mae_lped.DTO) AS XXX
" to "mae_lped.DTO" and add it to GROUP BY.
If Ido it that way, I get some lines repeated with different dates.
I can´t figur out why, cn anybody explain it. Is it, becaise the data is not the same, so it won´ñt group it, and if I change it into MAX(), it doesn´t need to be GROUPED BY?

Thx,

Johny

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-17 : 09:39:38

quote:

1) Every seller who has ever sold me the product
2) Only of the last DATE they offered/sold it.


SELECT MAE_ART.COD_ART AS ART, MAE_ART.DESCRIP AS DESCRIPCION, farm.nombre AS NOMBRE, mae_ped.COD_PED AS PEDIDO,
MAX(mae_lped.FECHA) AS FECHA
FROM mae_lped INNER JOIN
MAE_ART ON mae_lped.COD_ART = MAE_ART.COD_ART INNER JOIN
mae_ped ON mae_lped.COD_PED = mae_ped.COD_PED INNER JOIN
farm ON mae_ped.COD_PRO = farm.cod_farm
GROUP BY MAE_ART.COD_ART, MAE_ART.DESCRIP, farm.nombre, mae_ped.COD_PED
ORDER BY MAE_ART.COD_ART

What is this? its not the date, or the seller name. Drop it from the select list and it should work.

If not, follow the FIRST link in my signature, and ask your question again.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 10:05:12

And if you want to include it too, try like this:-

SELECT m.DTO,tmp.*
FROM mae_lped m
INNER JOIN
(
SELECT mae_lped.COD_ART, MAE_ART.DESCRIP, farm.nombre, farm.cod_farm, MAX(mae_lped.FECHA) AS Expr1, MAE_ART.COD_ART AS Expr2FROM MAE_ART INNER JOIN
mae_lped ON MAE_ART.COD_ART = mae_lped.COD_ART INNER JOIN
mae_ped ON mae_lped.COD_PED = mae_ped.COD_PED INNER JOIN
farm ON mae_ped.COD_PRO = farm.cod_farm
GROUP BY mae_lped.COD_ART, MAE_ART.DESCRIP, farm.nombre, farm.cod_farm, MAE_ART.COD_ART
)tmp
on tmp.cod_farm=m.COD_PRO
ORDER BY tmp.COD_ART

rtr1900
Starting Member

48 Posts

Posted - 2008-01-18 : 07:40:31

Thx you all!

Just another simple(?) question.
Is therer a way that when I have got the result in SQL server to get the result in a Excel file or TXT file?

Thx you all!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 08:18:11

quote:
Originally posted by rtr1900

Thx you all!

Just another simple(?) question.
Is therer a way that when I have got the result in SQL server to get the result in a Excel file or TXT file?

Thx you all!!!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

rtr1900
Starting Member

48 Posts

Posted - 2008-01-18 : 10:27:13

Thx madhivanan.

I found evenr something better http://www.download25.com/install/select-to-excel.html
(SQL2XL.zip)
It is an Excell book, where you just have to link tou your DB using ODBC. And then put the SQL query into it. I tried it out and it was just what I wanted, just keep in mind that you can´t get more than 65500 records as return.

regards to everybody out there!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources