Solved

SQL using MAX in one table and parameter in another

Posted on 2014-03-28
6
391 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

832 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