problem in select - SQL Server Forums

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

SQL Server Development (2000)

problem in select

Author

Topic

Dayvson
Starting Member

14 Posts

Posted - 2002-12-30 : 13:14:06

Hi people...I`m trying make a SELECT statement where the goal is bring the last done buying from each provider for the indicated product(this case, "B").Therefore, it`s bringing every buyings, When in fact it`s a mistake, check it up,please :

SELECT
TF.NAME,
max(TE.DATE) as Ultima_Data,
TP.DESCRIPTION_PRODUCT,
TI.PRICE,
TI.QUANTITY

FROM
TAB_ENTRANCE as TE INNER JOIN TAB_IT_ENTRANCE as TI
ON
TE.CODE_ENTRANCE = TI.CODE_ENTRANCE
INNER JOIN TAB_PROVIDERS AS TF
ON
TF.COD_PROVIDER = TE.COD_PROVIDER

INNER JOIN TAB_PRODUCT AS TP
ON
TP.CODE_PRODUCT = TI.CODE_PRODUCT
WHERE TP.DESCRIPTION_PRODUCT = 'B'
GROUP BY TI.PRICE,TI.QUANTITY,TF.NAME,TP.DESCRIPTION_PRODUCT
ORDER BY
TF.NAME

This case, it`s retrieving me the following results:

FornTest 02-12-12 B 4.7600 40
FornTest 02-12-09 B 5.0000 1000
JucaTudo 02-12-09 B 4.7600 200
JucaTudo 02-12-17 B 4.7600 450

How I said before, it should bring me only this:

FornTest 02-12-12 B 4.7600 40
JucaTudo 02-12-17 B 4.7600 450

Could someone help me?
Notes:I am not an expert in SQL, then, I`d like a little of patience from all you.Thanx!!!
I hope be helped! I apologize for any inconvenience.

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2002-12-30 : 13:20:27

Hi, Can you provide the table structure, so we can make some testing?

Sérgio Monteiro

Dayvson
Starting Member

14 Posts

Posted - 2002-12-30 : 13:29:58

Hi Sérgio, here is the structure of all tables... check it up:

TAB_PEDIDO_LOJA

CODIGO_PEDIDO_LOJA pk
CODIGO_LOJA
CODIGO_FUNCIONARIO fk1
DATA_PEDIDO
DATA_ENTREGA_PEDIDO

TAB_FUNCIONARIO

CODIGO_FUNCIONARIO pk
NOME_FUNCIONARIO
CARGO
LOGIN
SENHA

TAB_PEDIDO_AREA

CODIGO_PEDIDO_A pk
CODIGO_LOJA_APOIO
CODIGO_FUNCIONARIO fk1
DATA_PEDIDO
DATA_ENTREGA

TAB_IT_PEDIDO_A

CODIGO_IT_PEDIDO_A pk
CODIGO_PEDIDO_A fk1
CODIGO_PRODUTO fk2
QUANTIDADE
PRECO

TAB_IT_PEDIDO_LJ

CODIGO_IT_PEDIDO_LJ pk
CODIGO_PEDIDO_LJ fk1
CODIGO_PRODUTO fk2
QUANTIDADE
PRECO

TAB_PRODUTO

CODIGO_PRODUTO pk
DESCRICAO_PRODUTO
PRECO_PRODUTO
CODIGO_TIPO_VOLUME fk1
QTDE_MIM_PRODUTO

TAB_ESTOQUE_AREA

CODIGO_AREA fk1
CODIGO_PRODUTO fk1
QUANTIDADE

TAB_AREA

CODIGO_AREA pk
CODIGO_LOJA_APOIO

TAB_ESTOQUE

CODIGO_PRODUTO pk,fk1
QUANTIDADE

TAB_ENTRADA

CODIGO_ENTRADA pk
DATA_PEDIDO
DATA_ENTRADA

TAB_IT_ENTRADA

CODIGO_ENTRADA_IT pk
CODIGO_ENTRADA fk1
CODIGO_PRODUTO fk2
PRECO_ENTRADA
QUANTIDADE_ENTRADA

TAB_TIPO_VOLUME

CODIGO_TIPO_VOLUME pk
DESCRICAO_TIPO_VOLUME
QUANTIDADE_VOLUME

TAB_LOJA

CODIGO_LOJA pk
CODIGO_AREA fk1
NOME_LOJA
ENDERECO_LOJA
LOJA_APOIO

Note specially to sérgio: Sérgio, I just changed some fields when I posted the topic, just to make it easier , because it`s an english forum.If you can mail me, I thank you!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-30 : 18:19:16

The results your getting are because of the way you are grouping.
Your data is first grouped based on your statement
GROUP BY TI.PRICE,TI.QUANTITY,TF.NAME,TP.DESCRIPTION_PRODUCT
then functions like MAX are performed.

Drop this in Query Analyzer and see if you can follow this example:

CREATE TABLE #Test (ProductID int, ProductGroupID int, ProductName nvarchar(1), LastUpdated datetime, Quantity int)

INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (1,1,'a','20021228',1)
INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (1,1,'a','20021229',0)
INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (2,1,'a','20021230',7)
INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (2,1,'a','20021231',9)

INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (1,2,'a','20021228',1)
INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (1,2,'a','20021229',0)
INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (2,2,'a','20021230',7)
INSERT INTO #Test (ProductID, ProductGroupID, ProductName, LastUpdated, Quantity)
VALUES (2,2,'a','20021231',9)

SELECT A.ProductGroupID, MAX(A.LastUpdated) As MaxOfLastUpdated
FROM #Test A
GROUP BY A.ProductGroupID

SELECT B.ProductID, B.ProductGroupID, MAX(B.LastUpdated) As MaxOfLastUpdated
FROM #Test B
GROUP BY B.ProductID, B.ProductGroupID

SELECT C.ProductID, C.ProductName, C.ProductGroupID, C.LastUpdated, C.Quantity
FROM #Test C INNER JOIN
(
SELECT ProductGroupID, MAX(LastUpdated) As MaxOfLastUpdated
FROM #Test
GROUP BY ProductGroupID
) A ON C.ProductGroupID = A.ProductGroupID AND C.LastUpdated = A.MaxOfLastUpdated

DROP TABLE #Test

Edited by - ValterBorges on 12/30/2002 18:54:01

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-31 : 10:13:26

quote:

Hi valter, I`d like understand your answer to my question, where you did create
a table, and , others features,and drop it...What is your goal?May you explain
it me? Well, I am a brazilian guy, if you want to...We may talk in portuguese,
otherwise, we keep our contact in english. Try mail me..Thanx..C U ! ;(

Dayvson, Tudo Bem.
Eu vou escrever ingles porque o meu portugues escrito esta muinto fraco. So falo com a minha mae i e raro quando escrevo.

Do you know how to use Query Analyzer?
My example shows how different groupings affect the data that is returned. Also it shows one method of getting the max of a group and at the same time getting the rest of the information for the values that have the same max.

Dayvson, I'd rather you ask the question here instead of email because other people that might be having similar questions might will benefit from reading.

Edited by - ValterBorges on 12/31/2002 10:13:38

Dayvson
Starting Member

14 Posts

Posted - 2002-12-31 : 10:15:29

Well, I am still working on this...But, I use the access to help me in the queries, `cos sometimes I use this to take advantage about it...I got it in the access, But I am still having problems...Then, I`d like know the equivalent command to "Last" for SQL Default and works in the SQL Server. I am trying this at the Query Analyzer:

SELECT
TAB_FORNECEDORES.NOME,
Last(TAB_ENTRADA.DATA_PEDIDO) AS LastOfDATA_PEDIDO,
Last(TAB_IT_ENTRADA.PRECO_ENTRADA) AS LastOfPRECO_ENTRADA,
Last(TAB_IT_ENTRADA.QUANTIDADE_ENTRADA) AS LastOfQUANTIDADE_ENTRADA,
TAB_PRODUTO.DESCRICAO_PRODUTO
FROM
TAB_PRODUTO INNER JOIN ((TAB_FORNECEDORES INNER JOIN TAB_ENTRADA
ON
TAB_FORNECEDORES.COD_FORNECEDOR = TAB_ENTRADA.COD_FORNECEDOR) INNER JOIN TAB_IT_ENTRADA
ON
TAB_ENTRADA.CODIGO_ENTRADA = TAB_IT_ENTRADA.CODIGO_ENTRADA)
ON
TAB_PRODUTO.CODIGO_PRODUTO = TAB_IT_ENTRADA.CODIGO_PRODUTO
GROUP BY
TAB_FORNECEDORES.NOME, TAB_PRODUTO.DESCRICAO_PRODUTO
HAVING ((TAB_PRODUTO.DESCRICAO_PRODUTO = 'B'))

it`s retrieving me:
Server: Msg 195, Level 15, State 10, Line 3
'Last' is not a recognized function name.

PLEASE HELP ME!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-31 : 10:51:37

Why don't you use views it has a QBE type tool.

Also LAST is not valid in SQL Server, besides it would not give you want you want.

You want the lastest values based on Date therefore you must first figure out the max(date) for a group.
Last does not mean Latest it means the bottom most record from the group.

Take a look at the example and try to understand.

I don't have much time now so I will take your select statement and put it in this form and post it later.

SELECT C.ProductID, C.ProductName, C.ProductGroupID, C.LastUpdated, C.Quantity
FROM #Test C INNER JOIN
(
SELECT ProductGroupID, MAX(LastUpdated) As MaxOfLastUpdated
FROM #Test
GROUP BY ProductGroupID
) A ON C.ProductGroupID = A.ProductGroupID AND C.LastUpdated = A.MaxOfLastUpdated

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-01 : 22:47:17

Dayvson,

I started creating the ddl to show you the solution however I found a conflict.
I was about to create TAB_ENTRADA

In your 3rd post you described TAB_ENTRADA as

quote:

TAB_ENTRADA

CODIGO_ENTRADA pk
DATA_PEDIDO
DATA_ENTRADA

However in your 6th post you have the following on your join statement

TAB_FORNECEDORES.COD_FORNECEDOR = TAB_ENTRADA.COD_FORNECEDOR

Should this be

TAB_FORNECEDORES.COD_FORNECEDOR = TAB_ENTRADA.CODIGO_ENTRADA

or did you not post the correct table structure.

Well if you create the rest of ddl following the example below it will speed things up. Here is what I have so far.

CREATE TABLE #TAB_FORNECEDORES (COD_FORNECEDOR INT, NOME NVARCHAR(10))
INSERT INTO #TAB_FORNECEDORES (COD_FORNECEDOR, NOME) VALUES (1,'FornTest')
INSERT INTO #TAB_FORNECEDORES (COD_FORNECEDOR, NOME) VALUES (2,'JucaTudo')

CREATE TABLE #TAB_PRODUTO(CODIGO_PRODUTO INT, DESCRICAO_PRODUTO NVARCHAR(1))
INSERT INTO #TAB_PRODUTO (CODIGO_PRODUTO, DESCRICAO_PRODUTO) VALUES (1,'A')
INSERT INTO #TAB_PRODUTO (CODIGO_PRODUTO, DESCRICAO_PRODUTO) VALUES (2,'B')

DROP TABLE #TAB_FORNECEDORES
DROP TABLE #TAB_PRODUTO

Dayvson
Starting Member

14 Posts

Posted - 2003-01-02 : 09:59:52

hello Valter and all people tried help me... I thank all of you!! Well...I got it with this:

SELECT
TE.COD_FORNECEDOR,
TE.DATA_PEDIDO as Data,
TP.DESCRICAO_PRODUTO,
TI.PRECO_ENTRADA,
TI.QUANTIDADE_ENTRADA,
TF.NOME
FROM
TAB_ENTRADA as TE INNER JOIN TAB_IT_ENTRADA as TI
ON
TE.CODIGO_ENTRADA = TI.CODIGO_ENTRADA LEFT JOIN TAB_FORNECEDORES AS TF
ON
TF.COD_FORNECEDOR = TE.COD_FORNECEDOR INNER JOIN TAB_PRODUTO AS TP
ON
TP.CODIGO_PRODUTO = TI.CODIGO_PRODUTO
WHERE TP.DESCRICAO_PRODUTO = 'PRODUCT'
AND TE.DATA_PEDIDO = (SELECT MAX(TE.DATA_PEDIDO) FROM TAB_ENTRADA as TE
WHERE TE.COD_FORNECEDOR = TE.COD_FORNECEDOR)

Don`t worry friends... Certainly... I`ll be back!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-03 : 12:51:54

Yes the most two common methods to your problem is the derived table solution that I gave an example or the subquery as you have found.

You might find that the derived table join might perform better.

Also I think you have a slight mistake in your statement

(SELECT MAX(TE.DATA_PEDIDO) FROM TAB_ENTRADA as TE
WHERE TE.COD_FORNECEDOR = TE.COD_FORNECEDOR)

however it works because it's like saying where 1=1 which evaluates to true.

You were trying to do a correlated sub query which should reference the outer query so you need.

(SELECT MAX(TE.DATA_PEDIDO) FROM TAB_ENTRADA as TE2
WHERE TE2.COD_FORNECEDOR = TE.COD_FORNECEDOR)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources