Solved

SQL using MAX in one table and parameter in another

Posted on 2014-03-28
6
390 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
6 Comments
 
LVL 142

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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:
ScottPletcher earned 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now