• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

SQL using MAX in one table and parameter in another

Hi experts
I have 2 tables 'Session' and 'Count' , I want to retrieve the most recent Count.CountDate for any inputted Session.TrayNumber ie:

Session

Session_ID      TrayNumber
1001                 3
1002                 2
1003                 2
1004                 2

 Count

Session_ID      CountDate
1001             30/03/2014
1002             22/03/2014
1003             24/03/2014
1004             26/03/2014

So if I inputted Tray Number = 2 then I get the most recent date for that TrayNumber = 26/03/2014

What I have at the moment will only get the MAX CountDate for all the dates in the CountDate column ie:

SELECT *
FROM Session, Count
WHERE Session.Session_ID = Count.Session_ID
AND Count.CountDate = (SELECT MAX(CountDate)
FROM Count WHERE Session.TrayNumber = @TrayNumber)

Hope that this makes sense and someone can help.
0
dizzycat
Asked:
dizzycat
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think this article should be read:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

in code:
select c.Session_ID , c.CountDate
 from (
SELECT c.Session_ID , c.CountDate
, row_number() over (partition by c.Session_ID  order by c.CountDate desc) rn
FROM Session s
JOIN [Count] c
ON s.Session_ID = c.Session_ID
WHERE s.TrayNumber = @TrayNumber
) sq
where sq.rn = 1 

Open in new window

0
 
dizzycatAuthor Commented:
I'm very sorry but I forgot to mention i'm using the query in a Dataset which dosen't allow the use of Joins.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
no reason for the subquery:
select 1001 ID, 3 Tray into #temp

insert into #temp select 1002, 2
insert into #temp select 1003, 2
insert into #temp select 1004, 2

select 1001 Id, cast ('3/30/2014' as smalldatetime) CD into #temp2

insert into #temp2 select 1002, '3/22/2004'
insert into #temp2 select 1003, '3/26/2004'
insert into #temp2 select 1004, '3/30/2004'


-- final query
select max(CD) CD from #temp t
join #temp2 t2 on t.ID = t2.ID
where t.tray = 2 -- change to @trayNumber
group by t.tray


drop table #temp
drop table #temp2

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kyle AbrahamsSenior .Net DeveloperCommented:
Just a note it IS possible to join on tables in a dataset:
http://msdn.microsoft.com/en-us/library/bb386969(v=vs.110).aspx
0
 
Scott PletcherSenior DBACommented:
If you just need the date, you can do this:


DECLARE @TrayNumber int
SET @TrayNumber = 2

SELECT MAX(c.CountDate) AS LastCountDate
FROM [Count] c
WHERE
    c.Session_ID IN (
        SELECT s.Session_ID
        FROM Session s
        WHERE
            s.TrayNumber = @TrayNumber
        )


If you need other columns, let me know, and I will post the relevant query.
0
 
dizzycatAuthor Commented:
That's great, thanks.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now