CODEWITHSUNDEEP
sqlt-sql
nologo
nologo

Reputation: 6288

sql subquery group by

i need some assistance with a SQL query: the data looks like this:

ID (int, PK)
REF (int, FK)
UserName (varchar)
TransDate (DateTime)
Status (int)

1, 1001, joebloggs, 2011-12-15 13:50:01, 1
2, 1001, johnsmith, 2011-12-15 14:35:01, 1

so i need to return the REF and Username for the following; MIN(TransDate) and where status = 1.

so i do the following:

    SELECT  REF, UserName, MIN(TransDate )
    FROM          dbo.MyTable
    WHERE      (Status = 1)
    and REF = 1001
    GROUP BY REF, UserName

obviously this is wrong because it returns me:

1001, joebloggs, 2011-12-15 13:50:01
1001, johnsmith, 2011-12-15 14:35:01

however, i just need to return the username of the min transDate. so:

1001, joebloggs, 2011-12-15 13:50:01

can anyone help me please..its driving me insane.

thanks (from my sanity)

Upvotes: 2

Views: 1023

Answers (5)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

SELECT REF, UserName, TransDate
FROM dbo.MyTable    
WHERE ID = (
    SELECT TOP 1 ID
    FROM dbo.MyTable
    WHERE Status = 1 AND REF = 1001
    ORDER BY TransDate ASC
)

EDIT:

Or, if you need the results for each REF, instead of a specific REF, you can try this:

SELECT mt.REF, mt.UserName, mt.TransDate
FROM 
    dbo.MyTable mt JOIN (
        SELECT
            REF,
            MIN(TransDate) AS MinTransDate
        FROM dbo.MyTable
        WHERE Status = 1
        GROUP BY REF
    ) MinResult mr ON mr.REF = mt.REF AND mr.MinTransDate = mt.TransDate

Upvotes: 3

paulslater19
paulslater19

Reputation: 5917

Add an order by and a Limit 1:

Upvotes: -1

Twelfth
Twelfth

Reputation: 7180

Almost there. Use a subquery to locate the min_date, then join back to the original table for the rest of the data.

select * from dbo.mytable
inner join (select min(transdate)
FROM          dbo.MyTable
WHERE      (Status = 1)
and REF = 1001) a
on mytable.transdate = a.transdate

This solution will return multiple rows if you have multiple records with the same trans_date

Upvotes: 0

user554546
user554546

Reputation:

Try

SELECT  REF, UserName, MIN(TransDate )
FROM          dbo.MyTable
WHERE      (Status = 1)
and REF = 1001
and transdate=(select min(transdate) from dbo.MyTable)
GROUP BY REF, UserName;

Upvotes: 0

hspain
hspain

Reputation: 17568

How about doing it this way? 

SELECT TOP(1) REF, UserName, TransDate
FROM dbo.MyTable
WHERE (Status = 1)
and REF = 1001
ORDER BY TransDate

Upvotes: 2

Related Questions