?
Solved

SQL using MAX in one table and parameter in another

Posted on 2014-03-28
6
Medium Priority
?
397 Views
Last Modified: 2014-03-28
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
Comment
Question by:dizzycat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39961933
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
 

Author Comment

by:dizzycat
ID: 39961979
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39961987
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39961995
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39962132
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
 

Author Closing Comment

by:dizzycat
ID: 39962183
That's great, thanks.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, we’ll look at how to deploy ProxySQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question