min records - 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

General SQL Server Forums

New to SQL Server Programming

min records

Author

Topic

kidaduo
Starting Member

45 Posts

Posted - 2007-07-09 : 22:48:24

Hellow,

got a sql qry that is suppose to pick minmum PRECISEIDSCORE one by one from the table. The table could have 3 records with the same minimum PRECISEIDSCORE, but I will need to pick one at a time. Here is the qry but is not working and not sure what is the problem. Please, any suggestions

SELECT
MIN(T.PRECISEIDSCORE)
FROM
TBLEXPERIAN T
WHERE
T.EXPERIANANALYSTASSIGNED IS NULL
)

Thk

Josephine

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 22:53:33

quote:
pick minmum PRECISEIDSCORE one by one from the table

What do you mean one by one ?


SELECT MIN(T.PRECISEIDSCORE)
FROM   TBLEXPERIAN T
WHERE  T.EXPERIANANALYSTASSIGNED IS NULL

the min() will return you the minimum value, one value only.

Sorry, don't quite understand your problem here. Try posting some sample data and the result that you want.

KH
[spoiler]Time is always against us[/spoiler]

kidaduo
Starting Member

45 Posts

Posted - 2007-07-09 : 22:58:22

Thank u KH.

My tables has three records with PRECISEIDSCORE 55, 56, 55. I need to be able to pull only 55, then my next pull need to be second recods with 55, then my last has to be 56. I dont want to pull both 55 at one time. Attached is the complete proc.

ALTER PROCEDURE DBO.SP_ASSIGN_NEXT_EXPERIAN
(
@FRAUDANALYST NVARCHAR(50),
@PRECISEIDSCORE INT OUTPUT
)
AS

IF EXISTS
(
SELECT
PRECISEIDSCORE

FROM
TBLEXPERIAN
WHERE
EXPERIANANALYSTASSIGNED IS NULL
)

AND EXISTS
(
SELECT
*
FROM
FRAUD.DBO.tblFRAUDANALYST
WHERE
FULLNAME = @FRAUDANALYST
--AND STATUS = 'ACTIVE'
)

BEGIN

UPDATE TBLEXPERIAN
SET EXPERIANANALYSTASSIGNED = @FRAUDANALYST
WHERE
PRECISEIDSCORE IN (
SELECT
--O.PRECISEIDSCORE,
O.ACCT
FROM
TBLEXPERIAN O
JOIN
(
SELECT
PRECISEIDSCORE,
CUST_NM,
T.ACCT
FROM
TBLEXPERIAN T
WHERE
T.PRECISEIDSCORE=(

SELECT
MIN(T.PRECISEIDSCORE)
FROM
TBLEXPERIAN T
WHERE
T.EXPERIANANALYSTASSIGNED IS NULL
)

) AS NXT
ON
O.PRECISEIDSCORE = NXT.PRECISEIDSCORE OR
O.ACCT = NXT.ACCT OR
O.CUST_NM = NXT.CUST_NM OR
NXT.ACCT IN (SELECT
ACCT
FROM
TBLEXPERIAN
WHERE
EXPERIANANALYSTASSIGNED IS NULL
AND ( ACCT = O.ACCT
OR CUST_NM = O.CUST_NM)
)

WHERE EXPERIANANALYSTASSIGNED IS NULL)

SET @PRECISEIDSCORE = (SELECT
MAX(PRECISEIDSCORE)
FROM
TBLEXPERIAN
WHERE
EXPERIANANALYSTASSIGNED = @FRAUDANALYST
AND EXPERIANANALYSTWORKED IS NULL
)

END

GO

quote:
Originally posted by khtan

quote:
pick minmum PRECISEIDSCORE one by one from the table

What do you mean one by one ?

SELECT MIN(T.PRECISEIDSCORE)
FROM TBLEXPERIAN T
WHERE T.EXPERIANANALYSTASSIGNED IS NULL

the min() will return you the minimum value, one value only.

Sorry, don't quite understand your problem here. Try posting some sample data and the result that you want.

KH
[spoiler]Time is always against us[/spoiler]

Josephine

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 23:13:14

Can you work as a SET, rather than one by one ?


SELECT TOP 3 
         T.PRECISEIDSCORE
FROM     
         TBLEXPERIAN T
WHERE    
         T.EXPERIANANALYSTASSIGNED IS NULL
ORDER BY 
         T.PRECISEIDSCORE

KH
[spoiler]Time is always against us[/spoiler]

kidaduo
Starting Member

45 Posts

Posted - 2007-07-09 : 23:35:24

Thank u for your help,
I cannot work as a set- it has to be one by one.

Thk again

Jo

quote:
Originally posted by khtan

Can you work as a SET, rather than one by one ?

SELECT TOP 3
T.PRECISEIDSCORE
FROM
TBLEXPERIAN T
WHERE
T.EXPERIANANALYSTASSIGNED IS NULL
ORDER BY
T.PRECISEIDSCORE

KH
[spoiler]Time is always against us[/spoiler]

Josephine

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-10 : 14:49:29

quote:
Originally posted by kidaduo
I cannot work as a set- it has to be one by one.

I don't understand... Your stored procedure indicates that you're returning PRECISEIDSCORE as a single INT output parameter. How do you plan to do that if you're actually processing three rows? Which one of the three (55, 55, 56) do you return?

The only way you can process the three records individually in one call to the stored procedure is by using a cursor with the query that khtan posted to retrieve the data. That doesn't solve the problem of which value to return, though.

noorahmed.shaik
Starting Member

11 Posts

Posted - 2007-07-11 : 08:53:45

May b this query help u....

it will select the second minimum PRECISEIDSCORE

SELECT * FROM TBLEXPERIAN 
WHERE PRECISEIDSCORE=(SELECT MIN(SALARY) FROM TBLEXPERIAN  
WHERE PRECISEIDSCORE >(SELECT MIN(SALARY) FROM TBLEXPERIAN 
WHERE PRECISEIDSCORE >(SELECT MIN(SALARY) FROM TBLEXPERIAN)))


it will select the third minimum PRECISEIDSCORE

SELECT * FROM TBLEXPERIAN 
WHERE PRECISEIDSCORE=(SELECT MIN(SALARY) FROM TBLEXPERIAN  
WHERE PRECISEIDSCORE >(SELECT MIN(SALARY) FROM TBLEXPERIAN 
WHERE PRECISEIDSCORE >(SELECT MIN(SALARY) FROM TBLEXPERIAN
WHERE PRECISEIDSCORE >(SELECT MIN(SALARY) FROM TBLEXPERIAN))))

Noor Ahmed
Keysoft solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources