Stored Procedure Problem

Stored Procedure Problem

Post by Robin Hun » Mon, 26 Aug 2002 23:23:14



I have a table call TableA

Name            Phone Number
-----------------------------------
Jacky            27120342
Raymond      2803948
Jacky            90203478
Kitty               28376283
John               22393282

Select * from TableA where Name = 'Jacky' will return

Jacky    27120342
Jacky    90203478

What can I do if I just want to get the first number of each name?
(the query can be a Stored Procedure)

the correct result should be
Name            Phone Number
-----------------------------------
Jacky            27120342
Raymond      2803948
Kitty               28376283
John               22393282

Second Question:
I have a table TableA

Jigsaw Name                 piece
----------------------------------------
Micky Mouse                    1000
Micky Mouse                    1209
Micky Mouse                     200
Mario                                 900
Winny the Pooh               750
DingDong                         100
DingDong                          2500
Winny the Pooh                   500

Does anyone how can I select the 2 Max piece of Jigsaw?
(the query can be a Stored Procedure)
the result should be this:
Jigsaw Name                piece
----------------------------------------
Micky Mouse                    1000
Micky Mouse                    1209
Mario                                 900
Winny the Pooh              750
DingDong                         100
DingDong                          2500
Winny the Pooh                   500

Third Question
How can I find out the prduct code , Qty , Date with the (Max Qty)
TableA
Product Code         Qty                Date
-----------------------------------------------------
CP001                       5000            1/2/2002
CP001                       1000            2/1/2002
CP001                       2000            7/23/2000
CP003                       500              6/10/2000
CP004                       1000            5/16/1999
Cp004                        1500            6/10/2001

I know a way is like this:
Select * from tableA where Qty = (Select max(qty) from tableB where
tableA.productcode  = tableB.productcode)
But this method is quit slow. cos it is a sub-query statement anyone have a
better idea?
(the query can be a Stored Procedure)

Thank you.

 
 
 

Stored Procedure Problem

Post by Andrés Taylo » Tue, 27 Aug 2002 00:29:07


Robin,

It's a lot easier for everyone if you split up different questions in
different posts.

1:
SELECT *
FROM TableA a
WHERE [Phone Number] IN (
    SELECT TOP 1 [Phone Number]
    FROM TableA
    WHERE Name=a.Name
    ORDER BY [Phone Number]
)

2:
SELECT *
FROM JigSawPieces a
WHERE piece IN (
    SELECT TOP 2 [piece]
    FROM JigSawPieces
    WHERE [Jigsaw Name]=a.[Jigsaw Name]
    ORDER BY piece
)

3:
I don't think there's an easier way.

HTH,
--

Andrs Taylor


http://www.sql.nu/


Quote:> I have a table call TableA

> Name            Phone Number
> -----------------------------------
> Jacky            27120342
> Raymond      2803948
> Jacky            90203478
> Kitty               28376283
> John               22393282

> Select * from TableA where Name = 'Jacky' will return

> Jacky    27120342
> Jacky    90203478

> What can I do if I just want to get the first number of each name?
> (the query can be a Stored Procedure)

> the correct result should be
> Name            Phone Number
> -----------------------------------
> Jacky            27120342
> Raymond      2803948
> Kitty               28376283
> John               22393282

> Second Question:
> I have a table TableA

> Jigsaw Name                 piece
> ----------------------------------------
> Micky Mouse                    1000
> Micky Mouse                    1209
> Micky Mouse                     200
> Mario                                 900
> Winny the Pooh               750
> DingDong                         100
> DingDong                          2500
> Winny the Pooh                   500

> Does anyone how can I select the 2 Max piece of Jigsaw?
> (the query can be a Stored Procedure)
> the result should be this:
> Jigsaw Name                piece
> ----------------------------------------
> Micky Mouse                    1000
> Micky Mouse                    1209
> Mario                                 900
> Winny the Pooh              750
> DingDong                         100
> DingDong                          2500
> Winny the Pooh                   500

> Third Question
> How can I find out the prduct code , Qty , Date with the (Max Qty)
> TableA
> Product Code         Qty                Date
> -----------------------------------------------------
> CP001                       5000            1/2/2002
> CP001                       1000            2/1/2002
> CP001                       2000            7/23/2000
> CP003                       500              6/10/2000
> CP004                       1000            5/16/1999
> Cp004                        1500            6/10/2001

> I know a way is like this:
> Select * from tableA where Qty = (Select max(qty) from tableB where
> tableA.productcode  = tableB.productcode)
> But this method is quit slow. cos it is a sub-query statement anyone have
a
> better idea?
> (the query can be a Stored Procedure)

> Thank you.


 
 
 

Stored Procedure Problem

Post by Dan Guzma » Tue, 27 Aug 2002 00:46:31


Quote:> What can I do if I just want to get the first number of each name?

Assuming 'first' is the minimum Phone Number value for each name:

CREATE TABLE TableA(
    [Name] varchar(30) NOT NULL,
    [Phone Number] int NOT NULL)
GO
INSERT INTO TableA VALUES('Jacky', 27120342)
INSERT INTO TableA VALUES('Raymond', 2803948)
INSERT INTO TableA VALUES('Jacky', 90203478)
INSERT INTO TableA VALUES('Kitty', 28376283)
INSERT INTO TableA VALUES('John', 22393282)
GO
SELECT [Name], MIN([Phone Number])
FROM TableA
    GROUP BY [Name]
    ORDER BY [Name]
GO
DROP TABLE TableA
GO

Quote:> Does anyone how can I select the 2 Max piece of Jigsaw?

If Jigsaw Name and piece are unique:

CREATE TABLE TableA(
    [Jigsaw Name] varchar(30) NOT NULL,
    [piece] int NOT NULL)
GO
INSERT INTO TableA VALUES('Micky Mouse', 1000)
INSERT INTO TableA VALUES('Micky Mouse', 1209)
INSERT INTO TableA VALUES('Micky Mouse', 200)
INSERT INTO TableA VALUES('Mario', 900)
INSERT INTO TableA VALUES('Winny the Pooh', 750)
INSERT INTO TableA VALUES('DingDong', 100)
INSERT INTO TableA VALUES('DingDong', 2500)
INSERT INTO TableA VALUES('Winny the Pooh', 500)
GO

SELECT b.[Jigsaw Name], b.[piece]
FROM (SELECT [Jigsaw Name]
    FROM TableA
    GROUP BY [Jigsaw Name]) a
JOIN TableA b ON
    b.[Jigsaw Name] = a.[Jigsaw Name]
WHERE (SELECT COUNT(*)
    FROM TableA c
    WHERE c.[Jigsaw Name] = b.[Jigsaw Name] AND
        c.[piece] <= b.[piece]) <= 2
GO
DROP TABLE TableA
GO

Quote:> How can I find out the prduct code , Qty , Date with the (Max Qty)
> I know a way is like this:
> Select * from tableA where Qty = (Select max(qty) from tableB where
> tableA.productcode  = tableB.productcode)
> But this method is quit slow. cos it is a sub-query statement anyone
have a
> better idea?

Another method, assuming Product Code and qty are unique:

CREATE TABLE TableA(
    [Product Code] varchar(30) NOT NULL,
    [qty] int NOT NULL,
    [Date] smalldatetime NOT NULL)
GO
INSERT INTO TableA VALUES('CP001', 5000, '20020102')
INSERT INTO TableA VALUES('CP001', 1000, '20020201')
INSERT INTO TableA VALUES('CP001', 2000, '20000723')
INSERT INTO TableA VALUES('CP003', 500,  '20000610')
INSERT INTO TableA VALUES('CP004', 1000, '19990516')
INSERT INTO TableA VALUES('CP004', 1500, '20010610')
GO

SELECT a.*
FROM TableA a
JOIN (SELECT [Product Code], MAX([qty]) AS [qty]
    FROM TableA b
    GROUP BY [Product Code]) b ON
        b.[Product Code] = a.[Product Code] AND
        b.[qty] = a.[Qty]
GO

However, this may not perform any better than your original query.
Query performance depends on a number of factors, especially available
indexes.  Once you've developed the proper query, you can add indexes to
improve performance.  A non-clustered index on Product Code and qty will
cover this query and probably provide the best performance (unless you
index a view).  However, you'll need to determine the appropriate
balance between query performance and index maintenance costs.

BTW, these examples are missing some important information, especially
primary keys and related data.  Given this information, there may be
better methods to provide the desired results.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> I have a table call TableA

> Name            Phone Number
> -----------------------------------
> Jacky            27120342
> Raymond      2803948
> Jacky            90203478
> Kitty               28376283
> John               22393282

> Select * from TableA where Name = 'Jacky' will return

> Jacky    27120342
> Jacky    90203478

> What can I do if I just want to get the first number of each name?
> (the query can be a Stored Procedure)

> the correct result should be
> Name            Phone Number
> -----------------------------------
> Jacky            27120342
> Raymond      2803948
> Kitty               28376283
> John               22393282

> Second Question:
> I have a table TableA

> Jigsaw Name                 piece
> ----------------------------------------
> Micky Mouse                    1000
> Micky Mouse                    1209
> Micky Mouse                     200
> Mario                                 900
> Winny the Pooh               750
> DingDong                         100
> DingDong                          2500
> Winny the Pooh                   500

> Does anyone how can I select the 2 Max piece of Jigsaw?
> (the query can be a Stored Procedure)
> the result should be this:
> Jigsaw Name                piece
> ----------------------------------------
> Micky Mouse                    1000
> Micky Mouse                    1209
> Mario                                 900
> Winny the Pooh              750
> DingDong                         100
> DingDong                          2500
> Winny the Pooh                   500

> Third Question
> How can I find out the prduct code , Qty , Date with the (Max Qty)
> TableA
> Product Code         Qty                Date
> -----------------------------------------------------
> CP001                       5000            1/2/2002
> CP001                       1000            2/1/2002
> CP001                       2000            7/23/2000
> CP003                       500              6/10/2000
> CP004                       1000            5/16/1999
> Cp004                        1500            6/10/2001

> I know a way is like this:
> Select * from tableA where Qty = (Select max(qty) from tableB where
> tableA.productcode  = tableB.productcode)
> But this method is quit slow. cos it is a sub-query statement anyone
have a
> better idea?
> (the query can be a Stored Procedure)

> Thank you.

 
 
 

Stored Procedure Problem

Post by Robin Hun » Tue, 27 Aug 2002 01:19:46


OK Thanks for your reply.

> Robin,

> It's a lot easier for everyone if you split up different questions in
> different posts.

> 1:
> SELECT *
> FROM TableA a
> WHERE [Phone Number] IN (
>     SELECT TOP 1 [Phone Number]
>     FROM TableA
>     WHERE Name=a.Name
>     ORDER BY [Phone Number]
> )

> 2:
> SELECT *
> FROM JigSawPieces a
> WHERE piece IN (
>     SELECT TOP 2 [piece]
>     FROM JigSawPieces
>     WHERE [Jigsaw Name]=a.[Jigsaw Name]
>     ORDER BY piece
> )

> 3:
> I don't think there's an easier way.

> HTH,
> --

> Andrs Taylor


> http://www.sql.nu/



> > I have a table call TableA

> > Name            Phone Number
> > -----------------------------------
> > Jacky            27120342
> > Raymond      2803948
> > Jacky            90203478
> > Kitty               28376283
> > John               22393282

> > Select * from TableA where Name = 'Jacky' will return

> > Jacky    27120342
> > Jacky    90203478

> > What can I do if I just want to get the first number of each name?
> > (the query can be a Stored Procedure)

> > the correct result should be
> > Name            Phone Number
> > -----------------------------------
> > Jacky            27120342
> > Raymond      2803948
> > Kitty               28376283
> > John               22393282

> > Second Question:
> > I have a table TableA

> > Jigsaw Name                 piece
> > ----------------------------------------
> > Micky Mouse                    1000
> > Micky Mouse                    1209
> > Micky Mouse                     200
> > Mario                                 900
> > Winny the Pooh               750
> > DingDong                         100
> > DingDong                          2500
> > Winny the Pooh                   500

> > Does anyone how can I select the 2 Max piece of Jigsaw?
> > (the query can be a Stored Procedure)
> > the result should be this:
> > Jigsaw Name                piece
> > ----------------------------------------
> > Micky Mouse                    1000
> > Micky Mouse                    1209
> > Mario                                 900
> > Winny the Pooh              750
> > DingDong                         100
> > DingDong                          2500
> > Winny the Pooh                   500

> > Third Question
> > How can I find out the prduct code , Qty , Date with the (Max Qty)
> > TableA
> > Product Code         Qty                Date
> > -----------------------------------------------------
> > CP001                       5000            1/2/2002
> > CP001                       1000            2/1/2002
> > CP001                       2000            7/23/2000
> > CP003                       500              6/10/2000
> > CP004                       1000            5/16/1999
> > Cp004                        1500            6/10/2001

> > I know a way is like this:
> > Select * from tableA where Qty = (Select max(qty) from tableB where
> > tableA.productcode  = tableB.productcode)
> > But this method is quit slow. cos it is a sub-query statement anyone
have
> a
> > better idea?
> > (the query can be a Stored Procedure)

> > Thank you.

 
 
 

1. Stored procedures problem using DB2 Stored Procedure Builder

HI
 I am trying to run Stored Procedures using the DB2 Stored Procedure
utility provided with DB2. After picking up the corresponding table
and the columns when i try to run the stored procedure i get the
following message

 Create stored procedure returns -444.

[IBM][CLI Driver][DB2] SQL0444N  Routine "DSNTPSMP" (specific name "")
is implemented with code in library or path "", function "" which
cannot be accessed.  Reason code: "".  SQLSTATE=42724

Make sure that the SQL procedures processor, DSNTPSMP, is properly
installed on your DB2 for OS/390 host.

AGIN - Build failed.

Can anyone advice me as to where i might have overlooked something ?

Thanks
Arya

2. ADO 2.5

3. SQL Stored Procedure Problem

4. SQL on Paradox tables

5. Stored procedure problem.

6. Help for using xmla in visual studio .net RC

7. Stored Procedure problem (7.0)

8. Help! Error -956: My system doesen't trust itself

9. Extended Stored Procedure Problem

10. ADOX and Stored Procedure Problem

11. HELP! Stored Procedure Problem - Works then Breaks

12. Stored procedure problem

13. Stored Procedure Problems