can´t get 3 rows only - 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

Transact-SQL (2000)

can´t get 3 rows only

Author

Topic

rtr1900
Starting Member

48 Posts

Posted - 2008-06-10 : 03:35:11

Hi,

I have a code, which has to return just the latest 3 rows (3 latest sales).
But it doesn´t do that. Can anybody help me on this code where I have it wrong? Thx


SELECT     T.Cod_Art, T.cant, T.Dto, T.descrip, MAE_PRO.COD_PRO, MAE_PRO.NOMBRE, T.fec_stock
FROM         (SELECT     a.Cod_Art, a.fecha, a.Cod_Lab, a.cant, a.Dto, a.descrip, a.cod_ped, a.fec_stock
                       FROM          mae_lped_susan2008 a INNER JOIN
                                              mae_Art b ON a.Cod_Art = b.Cod_Art
                       WHERE      a.fec_stock >= '01/07/2007' AND a.fec_stock <= '10/06/2008'
                       GROUP BY a.Cod_Art, a.fecha, a.Cod_Lab, a.cant, a.dto, a.descrip, a.cod_ped, a.fec_stock
                       HAVING      COUNT(*) BETWEEN 1 AND 3) T INNER JOIN
                      mae_ped ON T.cod_ped = mae_ped.COD_PED INNER JOIN
                      MAE_PRO ON mae_ped.COD_PRO = MAE_PRO.COD_PRO
GROUP BY T.Cod_Art, T.Dto, T.descrip, T.cant, MAE_PRO.COD_PRO, MAE_PRO.NOMBRE, T.fec_stock
ORDER BY T.Cod_Art, T.fec_stock DESC

To explain a bit, i have a table with the articles and another with the lines (=each article) of the order(s) (=mae_lped_susan2008) of that article. And I just want to return the latest 3 ones I have made.

Now it works fine, just that it doesnñt respect the 3 rows, it returns all the rows found between the dates.

Thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 03:55:16

Can you provide some sample data to ilustrate what you're trying to achieve?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 04:10:33

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

rtr1900
Starting Member

48 Posts

Posted - 2008-06-10 : 04:26:20

quote:
Originally posted by visakh16

Can you provide some sample data to ilustrate what you're trying to achieve?

Thx for responding. here is what I got with my code


COD_ART	CANTIDAD	DTO	DESCRIP	COD_PRO	NOMBRE	FEC_STOCK
2015	46	0	MALETIN D	999	xxxxxxxxxxxxxxxxxxxx	31/12/2007
2015	50	0	MALETIN D	2	xxxxxxxxxxxxxxxxxxxx	02/08/2007
2015	100	0	MALETIN D	2	xxxxxxxxxxxxxxxxxxxx	04/07/2007
2016	1152	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	12/03/2008
2016	-576	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	25/01/2008
2016	576	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	25/01/2008
2016	1146	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	23/10/2007
2101	1600	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	11/02/2008
2101	400	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	01/02/2008
2101	3	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	17/01/2008
2101	1600	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	15/01/2008
2101	4	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	19/12/2007
2101	800	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	14/12/2007
2101	1600	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	23/11/2007
2101	1600	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	13/09/2007
2101	2000	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	11/07/2007
2500	102	0	DUREX PLAY VIBRAT	170	xxxxxxxxxxxxxxxxxxxx	01/10/2007
2714	-158	0	CARAMELOS SIN AZUCAR LA PIREN. EUCALIP ¡	27	xxxxxxxxxxxxxxxxxxxx	22/04/2008

where you can see it return for the article 2201 several lines, when it should only return the latest 3 ones (using fec_stock)
So it should have returned this


COD_ART	CANTIDAD	DTO	DESCRIP	COD_PRO	NOMBRE	FEC_STOCK
2015	46	0	MALETIN D	999	xxxxxxxxxxxxxxxxxxxx	31/12/2007
2015	50	0	MALETIN D	2	xxxxxxxxxxxxxxxxxxxx	02/08/2007
2015	100	0	MALETIN D	2	xxxxxxxxxxxxxxxxxxxx	04/07/2007
2016	1152	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	12/03/2008
2016	-576	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	25/01/2008
2016	576	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	25/01/2008
2016	1146	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	23/10/2007
2101	1600	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	11/02/2008
2101	400	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	01/02/2008
2101	3	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	17/01/2008
2500	102	0	DUREX PLAY VIBRAT	170	xxxxxxxxxxxxxxxxxxxx	01/10/2007
2714	-158	0	CARAMELOS SIN AZUCAR LA PIREN. EUCALIP ¡	27	xxxxxxxxxxxxxxxxxxxx	22/04/2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 07:06:39

Why do you all return all the four records in case of COD_ART=2016. didnt understand what's the rule for duplicates. I would have expected o/p to contain only first three lines of 2016

rtr1900
Starting Member

48 Posts

Posted - 2008-06-10 : 07:23:58

quote:
Originally posted by visakh16

Why do you all return all the four records in case of COD_ART=2016. didnt understand what's the rule for duplicates. I would have expected o/p to contain only first three lines of 2016

Correct, i removed manually the rows dat didn´t belong there in the endresult. Forgot that one.
So it shold have been


COD_ART	CANTIDAD	DTO	DESCRIP	COD_PRO	NOMBRE	FEC_STOCK
2015	46	0	MALETIN D	999	xxxxxxxxxxxxxxxxxxxx	31/12/2007
2015	50	0	MALETIN D	2	xxxxxxxxxxxxxxxxxxxx	02/08/2007
2015	100	0	MALETIN D	2	xxxxxxxxxxxxxxxxxxxx	04/07/2007
2016	1152	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	12/03/2008
2016	-576	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	25/01/2008
2016	576	0	CONTROL ADAPTA NATURE 3 UNID.	44	xxxxxxxxxxxxxxxxxxxx	25/01/2008
2101	1600	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	11/02/2008
2101	400	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	01/02/2008
2101	3	0	TERMOMETRO EXTRA ORO,	5	xxxxxxxxxxxxxxxxxxxx	17/01/2008
2500	102	0	DUREX PLAY VIBRAT	170	xxxxxxxxxxxxxxxxxxxx	01/10/2007
2714	-158	0	CARAMELOS SIN AZUCAR LA PIREN. EUCALIP ¡	27	xxxxxxxxxxxxxxxxxxxx	22/04/2008

Sorry for that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 08:29:44

[code]SELECT T.Cod_Art, T.cant, T.Dto, T.descrip, MAE_PRO.COD_PRO, MAE_PRO.NOMBRE, T.fec_stock INTO #Temp
FROM (SELECT a.Cod_Art, a.fecha, a.Cod_Lab, a.cant, a.Dto, a.descrip, a.cod_ped, a.fec_stock
FROM mae_lped_susan2008 a INNER JOIN
mae_Art b ON a.Cod_Art = b.Cod_Art
WHERE a.fec_stock >= '01/07/2007' AND a.fec_stock <= '10/06/2008'
GROUP BY a.Cod_Art, a.fecha, a.Cod_Lab, a.cant, a.dto, a.descrip, a.cod_ped, a.fec_stock
HAVING COUNT(*) BETWEEN 1 AND 3) T INNER JOIN
mae_ped ON T.cod_ped = mae_ped.COD_PED INNER JOIN
MAE_PRO ON mae_ped.COD_PRO = MAE_PRO.COD_PRO
GROUP BY T.Cod_Art, T.Dto, T.descrip, T.cant, MAE_PRO.COD_PRO, MAE_PRO.NOMBRE, T.fec_stock
ORDER BY T.Cod_Art, T.fec_stock DESC

SELECT * FROM
(SELECT (SELECT COUNT(*) FROM #Temp
WHERE COD_ART=t.COD_ART
AND COD_PRO=t.COD_PRO
AND FEC_STOCK<t.FEC_STOCK) + 1 AS Seq,
*
FROM #Temp t)r
WHERE r.Seq <=3[/code]

rtr1900
Starting Member

48 Posts

Posted - 2008-06-12 : 05:58:12

thx visakh16,

I tried the code, but it keeps giving me multiple rows (more than 3 I mean).
I will try to mess around a bit with the codes and come back later to give you some feedback.

Thx already for trying to help me!

rtr1900
Starting Member

48 Posts

Posted - 2008-06-12 : 06:49:09

Hi visakh16,

I changed the code to

SELECT     *
FROM (SELECT     (SELECT     COUNT(*)
                   FROM         #Temp  WHERE     
                    COD_ART = t .COD_ART AND 
                    COD_PRO < t .COD_PRO) + 1 AS Seq, *
       FROM          #Temp t) r
WHERE     (Seq <= 3)

And removing the line

AND COD_PRO=t.COD_PRO

seems to do the job.
I have checked the rows (more than 40.000) and I haven´t found any product with more than 3 lines in it. So I guess it works fine.

Thx again for helping me this timne (and the other time also)!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources