This SQL doesn't work, help! - 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

This SQL doesn't work, help!

Author

Topic

Erock
Starting Member

2 Posts

Posted - 2012-05-14 : 16:33:22

I am trying to assemble a query that returns a single 'ComapnyName' for each 'JobID' (there is a one to many relationship JobID to CompanyName).

Here is the statement I am trying to use:

SELECT i.JobId as JobNumber, i.PercentCovered, i.isPrimary, i.CompanyId
FROM InsuranceLayer as i
WHERE i.isPrimary = 1 AND i.PercentCovered IS NOT NULL
AND EXISTS (Select TOP 1 CompanyID FROM InsuranceLayer WHERE JobId = i.JobId ORDER BY PercentCovered DESC)
ORDER BY i.JobID

Which I think should isolate a single CompanyID per JobID via the exist statement, but its not working...

If I execute the EXIST piece on its own with a JobID, it returns a single row, so I assume the problem is with the execution of the EXIST clause?

Any help would be greatly appreciated.

E

Erock
Starting Member

2 Posts

Posted - 2012-05-14 : 17:02:45

Solved!

I found this syntax in another thread:

SELECT(
[ROWNUM] = ROW_NUMBER() OVER(PARTITION BY JobID ORDER BY i.PercentCovered DESC)
) as Table

Where Table.ROWNUM = 1

Which did the trick!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 17:33:38

quote:
Originally posted by Erock

Solved!

I found this syntax in another thread:

SELECT(
[ROWNUM] = ROW_NUMBER() OVER(PARTITION BY JobID ORDER BY i.PercentCovered DESC)
) as Table

Where Table.ROWNUM = 1

Which did the trick!

Using ROW_NUMBER like you are attempting to do is a common approach. The syntax would be like this:

;WITH cte AS
(
SELECT i.JobId AS JobNumber,
       i.PercentCovered,
       i.isPrimary,
       i.CompanyId,
       ROW_NUMBER() OVER (PARTITION BY JobId ORDER BY i.PercentCovered DESC) AS RN
FROM   InsuranceLayer AS i
WHERE  i.isPrimary = 1
       AND i.PercentCovered IS NOT NULL
)
SELECT
	JobId AS JobNumber,
	PercentCovered,
	isPrimary,
	CompanyId
FROM
	cte
WHERE
	RN=1
ORDER BY
	JobID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources