need help with max function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help with max function

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-05-06 : 13:16:12

I am trying to create a report that is using 2 tables. The first table is called equipdaily. The 2 fields I am using are Equipdaily.kequipnumber, the second one is equipdaily.equipstatus. I then need to join into another table called Equipdet. The field I link with the Pk is (kequipnumber). The Field I grabbed from that table was equipdet.kordernumber. The problem I am having is we have rented out these equip to other people and its pulling all data. I want the most recent one. So I am assuming I need to use the max code. Here is what I have so far and it works I just dont know how to add the Max to either equipnum or if it should be added to ordernum. please help me out.

SELECT DISTINCT equipdet.kequipnum, equipdet.kordnum, equipdaily.eqpstatus
FROM equipdaily INNER JOIN
equipdet ON equipdaily.kequipnum = equipdet.kequipnum
WHERE (equipdaily.eqpstatus = 'ON')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 13:41:57

Post some sample data to illustrate your problem. Show us the multiple records and then which rows you want the query to output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-05-06 : 13:46:15

kequipnum Kordnum eqpstatus
1694L, 1001934, ON
1694L, 1002251, ON
1694L, 1005685, ON
1728LKE, 0 , ON
1728LKE, 1005648, ON
1728LKE, 1012082, ON
1728LKE, 1017286, ON
1728LKE, 5000709, ON
1728LKE, 5001725, ON
1728LKE, 5001865, ON
1728LKE, 5002822, ON

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 13:48:05

quote:
Originally posted by tkizer

Show us the multiple records and then which rows you want the query to output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-05-06 : 13:50:54

I want it to output the last record of each to display only. for example
1694L, 1005685, ON
1728LKE, 5002822, ON

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 13:55:54

Try this:


SELECT equipdet.kequipnum, equipdet.kordnum, equipdaily.eqpstatus
FROM equipdaily 
INNER JOIN equipdet ON equipdaily.kequipnum = equipdet.kequipnum
INNER JOIN
(
	SELECT kequipnum, MAX(kordnum) AS kordnum
	FROM equipdet
	GROUP BY kequipnum	
) dt
ON equipdet.kequipnum = dt.kequipnum AND equipdet.kordnum = dt.kordnum
WHERE equipdaily.eqpstatus = 'ON'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-05-06 : 14:01:25

Here is the results of what you created It did reduce it some but not fully
4630LKE, 1603157, ON
4630LKE, 1603157, ON
4601LKE, 1019732, ON
4601LKE, 1019732, ON
4527, 5007828, ON
4527, 5007828, ON
4523LKE, 1502789, ON
4523LKE, 1502789, ON
4522, 2004884, ON
4522, 2004884, ON
4522, 2004884, ON
4522, 2004884, ON
4513, 1502619, ON

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-05-06 : 14:02:36

If I add the distinct feature to this would that fix it?

SELECT Distinct equipdet.kequipnum, equipdet.kordnum, equipdaily.eqpstatus
FROM equipdaily
INNER JOIN equipdet ON equipdaily.kequipnum = equipdet.kequipnum
INNER JOIN
(
SELECT kequipnum, MAX(kordnum) AS kordnum
FROM equipdet
GROUP BY kequipnum
) dt
ON equipdet.kequipnum = dt.kequipnum AND equipdet.kordnum = dt.kordnum
WHERE equipdaily.eqpstatus = 'ON'

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-05-06 : 14:03:27

also what does dt stand for?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 14:06:05

I'm just aliasing the derived table. You can name it anything, I just typically use t or dt.

Adding DISTINCT will fix your issue as I understand it, however I prefer not to do that as we can usually fix it by fixing the grouping. But why don't you just try adding DISTINCT to see if it fixes your issue rather than asking us?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-05-06 : 14:36:18

yeah that worked very well with your filter on the Max and placing the distinct under the main select statement it cleared every thing up. instead of 400 records it took it down to 90 current records. Thank you again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources