SQL Server mimimum of all positive values

Question

Hi I have a table like this:

tblMembers
ID   Credit  Is_Member
---  ---     --- 
1    45      True
2    20      False
3    25      True
4    -10     True
5    -5      False
6    13      True

How can I create a view showing the record with the (Minimum Positive) value of Credit and Is_Member="True"?

Thanks!

Answer 1

select min(credit) 
from tblMembers
where Is_Member = 'true'
    and credit > 0

...and if this is homework, please tag it as such (as answers may be different).

EDIT

Didn't read the question correctly.

select * from tblMembers
where credit = (select min(credit)
    from tblMembers
    where credit > 0
        and Is_Member = 'true')
and Is_Member = 'true'

...you need the check for Is_Member = 'true' in both spots because the lowest credit score may not be a member (which would net no results) AND a non-member and member may share the lowest score but only a member should show.

Answer 2

Am I missing something? It seems straightforward enough:

select min(Credit) as Credit 
from tblMembers 
where Is_Member = 'True' and Credit >= 0

Answer 3

SELECT MIN(Credit), ID FROM tblMembers WHERE Is_Member="True"
GROUP BY ID HAVING MIN(Credit) > 0

I think I might be oversimplifying this, though. Use at your own risk.

Answer 4

There could be multiple records that meet this criteria...

SELECT *
FROM tblMembers
WHERE Credit = (
    SELECT  MIN(Credit)
    WHERE   Credit > 0
        AND Is_Member = 'True'
)

Answer 5

You'd use a query that looks something like this:

SELECT *
FROM tblMembers
WHERE Credit = (SELECT MIN(Credit)
    FROM tblMembers
    WHERE Is_Member = TRUE AND Credit > 0)
AND Is_Member = TRUE

Note that multiple rows could be tied for the lowest positive value, so you may get more than one result.

Answer 6

SELECT * FROM tblMembers
WHERE Credit = 
(SELECT MIN(Credit) FROM tblMembers WHERE Credit > 0 AND Is_Member = 'True')
AND Is_Member = 'True'

Note that if you have more than one row with the same (minimum) credit, that would return more than one row.

Answer 7

You are liable to have multiple records that fit your criteria. If you only want a single row:

create view PositiveCreditMember
as
select top 1
    ID
,   credit
,   is_member
from
    tblMembers
where 
    credit in (
      select
         min(Credit) 
      from
         tblMembers
      where
        credit > 0
        and is_member = 'True'
    )
    and is_member = 'True'
order by ID

Answer 8

or

select * from credit
where is_member = 'true'
and   credit > 0
order by credit
limit 1

aka

select top 1 * from credit
where is_member = 'true'
and   credit > 0
order by credit

depending on your flavor of SQL. This gives only one record; as others have said there may be multiple records with the minimum value.

Answer 9

What about:

CREATE VIEW vwXYZ
AS
SELECT TOP 1 *
FROM tblMembers
WHERE [Is_Member] = 1
AND Credit > 0
ORDER BY Credit;

This eliminates the subquery.

Answer 10

If you want one of the matching rows at random:

select top 1 * from tblMembers where Is_Member = 1 order by credit

or if you want all the matching rows:

select top 1 with ties * from tblMembers where Is_Member = 1 order by credit