CODEWITHSUNDEEP
sql-serversql-server-2008
visevo
visevo

Reputation: 861

SQL Min value not being selected

I have a select statement with a joining table, and I am attempting to select the first row of the joined table.

For example, dbo.Projects has many dbo.Buffers.

My query is:

SELECT PM.PROJECTID, PM.PROJECTNAME, BU.PERCENTPENWREALIGNED
FROM dbo.PROJECTMGRVIEW AS PM
JOIN dbo.S2M_BUFFER AS BU ON BU.PROJECTID = ( SELECT DISTINCT MIN(TASKUNIQUEID) FROM dbo.S2M_BUFFER WHERE PROJECTID = PM.PROJECTID )
WHERE PM.PROJECT_TYPE = 8 AND PM.CATEGORY = 'Engineering' ANd PM.PROJECTID = 244;

My result set is many rows:

PROJECTID | PROJECTNAME | PERCENTPENWREALIGNED
244       | PROJECT A   | 100
244       | PROJECT A   | 0
244       | PROJECT A   | 0
244       | PROJECT A   | 0
244       | PROJECT A   | 0
244       | PROJECT A   | 0
244       | PROJECT A   | 0

Obviously in this case, I simply need the first row.

Upvotes: 0

Views: 38

Answers (2)

Prometheus
Prometheus

Reputation: 56

I agree your Join statement seems flawed due a circular reference between the PROJECTID and the TASKUNIQUEID.

I think this might be more what you were trying to do:

SELECT PM.PROJECTID, PM.PROJECTNAME, BU.PERCENTPENWREALIGNED
FROM dbo.PROJECTMGRVIEW AS PM
JOIN dbo.S2M_BUFFER AS BU ON BU.TASKUNIQUEID = 
( SELECT DISTINCT MIN(TASKUNIQUEID) FROM dbo.S2M_BUFFER WHERE PROJECTID = PM.PROJECTID )
WHERE PM.PROJECT_TYPE = 8 AND PM.CATEGORY = 'Engineering' ANd PM.PROJECTID = 244;

Upvotes: 0

James Z
James Z

Reputation: 12317

Your join doesn't really make any sense, you can't do it using a subquery, but my guess is you want something like this:

SELECT PM.PROJECTID, PM.PROJECTNAME, BU.PERCENTPENWREALIGNED
FROM dbo.PROJECTMGRVIEW AS PM
CROSS APPLY (
  select top 1 PERCENTPENWREALIGNED
  from dbo.S2M_BUFFER BU 
  where BU.PROJECTID = PM.PROJECTID
  order by TASKUNIQUEID ASC
) BU
WHERE PM.PROJECT_TYPE = 8 AND PM.CATEGORY = 'Engineering' AND PM.PROJECTID = 244;

This will join the row with smallest TASKUNIQUEID in S2M_BUFFER with the PROJECTMGRVIEW

Upvotes: 1

Related Questions