Getting the Next Available Row

Question

How can I get a List all the JobPositionNames having the lowest jobPositionId when ContactId = 1

Tablel :

| JobPositionId | JobPositionName | JobDescriptionId | JobCategoryId | ContactId
---------------------------------------------------------------------------------
       1        |  Audio Cables   |      1           |      1        |     1
       2        |Audio Connections|      2           |      1        |     1
       3        |Audio Connections|      2           |      1        |     0
       4        |Audio Connections|      2           |      1        |     0
       5        |   Sound Board   |      3           |      1        |     0
       6        |   Tent Pen      |      4           |      3        |     0

eg the result of this table should be lines 1,3,5,6

Answer 1

I can't figure out the solution. Only lack of something, but I can give some code for you view. Maybe it can help you.

--create table
create table t
(
JobPositionId int identity(1,1) primary key,
JobPositionName nvarchar(100) not null,
JobDescriptionId int,
JobCategoryId int,
ContactId int
)
go

--insert values
BEGIN TRAN

INSERT INTO t VALUES ('AudioCables', 1,1,1)
INSERT INTO t VALUES ('AudioConnections',2,1,1)
INSERT INTO t VALUES ('AudioConnections',2,1,0)
INSERT INTO t VALUES ('AudioConnections',2,1,0)
INSERT INTO t VALUES ('SoundBoard',3,1,0)
INSERT INTO t VALUES ('TentPen',4,3,0)
COMMIT TRAN
GO

SELECT 
    Min(JobPositionId) AS JobPositionId, JobPositionName, ContactId
INTO
    #tempTable
FROM 
    t
GROUP BY JobPositionName, ContactId

SELECT * FROM #tempTable
WHERE JobPositionId IN (
    SELECT JobPositionId
    FROM #tempTable
    GROUP BY JobPositionName
    --... lack of sth, I can't figure out ,sorry.
)

drop table t
GO

Answer 2

For per-group maximum/minimum queries you can use a null-self-join as well as strategies like subselects. This is generally faster in MySQL.

SELECT j0.JobPositionId, j0.JobPositionName, j0.ContactId
FROM Jobs AS j0
LEFT JOIN Jobs AS j1 ON j1.JobPositionName=j0.JobPositionName
AND (
    (j1.ContactId<>0)<(j0.ContactId<>0)
    OR ((j1.ContactId<>0)=(j0.ContactId<>0) AND j1.JobPositionId<j0.JobPositionId))
)
WHERE j1.JobPositionName IS NULL

This says, for each JobPositionName, find a row for which there exists no other row with a lower ordering value. The ordering value here is a composite [ContactId-non-zeroness, JobPositionId].

(Aside: shouldn't JobPositionName and JobCategoryId be normalised out into a table keyed on JobDescriptionId? And shouldn't unassigned ContactIds be NULL?)

Answer 3

SELECT  jp.*
FROM    (
        SELECT  JobPositionName, JobPositionId, COUNT(*) AS cnt
        FROM    JobPosisions
        ) jpd
JOIN    JobPosisions jp
ON      jp.JobPositionId =
        IF(
        cnt = 1,
        jpd.JobPositionId,
        (
        SELECT  MIN(JobPositionId)
        FROM    JobPositions jpi
        WHERE   jpi.JobPositionName = jpd.JobPositionName
                AND jpi.ContactID = 0
        )
        )

Create an index on (JobPositionName, ContactId, JobPositionId) for this to work fast.

Note that if will not return the jobs having more than one position, neither of which has ContactID = 0