General SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesGeneral SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old March 31st, 2005, 02:29 PM
Euclid Euclid is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2005
Posts: 1 Euclid User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 14 m 2 sec
Reputation Power: 0
Selecting Max Date for Many Records

I've got a table that's a list of clients waiting in line. Let's say the table has 4 fields:
(obligatory unique id),client name,worker, time

As the client's make their way though the facility, they see different workers and each worker makes a new entry in this table, with the current time, showing when they saw the client. Later on, this data will be used to analyse where clients spend the most time, so we can't overwrite any records.

What I need is a query that gets only the newest entry for each client.

so in a table like this:
bob, worker1, 1pm
bob, worker2, 2pm
jim, worker1, 3pm
jim, worker2, 4pm
jim, worker3, 5pm

The correct query would return only:
bob, worker2, 2pm
jim, worker3, 5pm

I've tried several different things with Max(time), and intersections, but I can't find a good solution. Surely there's a way to put it all in one sql statement.

Anyone have any ideas?
Thanks!

Reply With Quote
  #2  
Old April 25th, 2005, 01:25 PM
dejaone dejaone is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2005
Posts: 34 dejaone User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 47 m 11 sec
Reputation Power: 13
try "group by" on client field and max(time) in "where" clause.

Reply With Quote
  #3  
Old April 25th, 2005, 06:52 PM
Madpawn Madpawn is offline
My beat is correct.
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 339 Madpawn User rank is Private First Class (20 - 50 Reputation Level)Madpawn User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 13
You cannot use aggregate functions in WHERE clauses. Try a subquery:

Code:
 SELECT
  client name,
  worker,
  `time`
 FROM
  yourtable
 WHERE
  `time` IN
 	(SELECT
 	   MAX(`time`)
 	 FROM
 	   yourtable
 	 GROUP BY
 	   client name)
 


I realize this is probably is pseudotable, but I'd like to point out a couple of other things. TIME is a reserved word, so it's best not to use it as a column name. Also, I wouldn't recommend storing times as you've indicated here -- you should use a TIME type. If you store them as nAM/PM strings, your MAX() function won't work correctly.
__________________
"A pawn is the most important piece on the chessboard -- to a pawn"


Reply With Quote
  #4  
Old March 28th, 2008, 09:29 AM
casbornsen casbornsen is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2008
Posts: 1 casbornsen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 m
Reputation Power: 0
Thanks!

I had a problem with Army MOTEs having multiple dates on a single table and I only wanted to use the most recent one. The subquery you posted worked great! Hooah!

Reply With Quote
  #5  
Old November 18th, 2008, 04:07 PM
Sneeriepoo Sneeriepoo is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2008
Posts: 1 Sneeriepoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 39 sec
Reputation Power: 0
Another related question...

I have a closely related question.

I need to figure out how to compare two dates/times in SQL and only filter/return the line which happened most recently before the first date/time.

Here is a simple example of the data:

Table A:
Who, Time of Query, Date of Query
Bob, 3pm, 9/5/08
Bob, 7pm, 9/5/08
Jack, 2pm, 9/8/08
Jack 5pm, 9/8/08
Jack 5pm, 9/10/08

Table B:
Who, Time of Action, Date of Action, Action
Bob, 4pm, 9/5/08, Swimming
Bob, 2pm, 9/5/08, Drinking
Bob 11pm, 9/9/08, Sleeping
Jack 1pm, 9/7/08, School
Jack 3pm, 9/8/08, Play
Jack 11pm, 9/10/08, Sleeping

The results would be:

Bob, 3pm, 9/5/08, Drinking
Bob, 7pm, 9/5/08, Swimming
Jack, 2pm, 9/8/08, School
Jack 5pm, 9/8/08, Play
Jack 5pm, 9/10/08, Play

Where do I put the logic in the embedded select query to make the comparison using WHERE 'Time' IN using the example below?

Reply With Quote
  #6  
Old September 7th, 2011, 10:25 PM
jonny rottan jonny rottan is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Sep 2011
Posts: 1 jonny rottan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 27 m 34 sec
Reputation Power: 0
Quote:
Originally Posted by Madpawn

Code:
 SELECT
  client name,
  worker,
  `time`
 FROM
  yourtable
 WHERE
  `time` IN
 	(SELECT
 	   MAX(`time`)
 	 FROM
 	   yourtable
 	 GROUP BY
 	   client name)
 




Hi,

I have tried to implement the example but I am still getting multipl dates. I include the code that I am using below:
Code:
SELECT  
Inspection_Date,
Formatted_Licence_Number,
Licencee,
Description,
Major_Code	

FROM
Infodbo.Licensing_Facts t1
INNER JOIN Infodbo.Inspections_Facts t2
ON t1.Licensing_Facts_Key=t2.Licensing_Facts_Key
INNER JOIN Infodbo.Licensing_Categories t3
ON t2.Licensing_Facts_Key=t3.Licensing_Facts_Key

WHERE      (Inspection_Date IN (SELECT MAX (Inspection_Date) FROM 

Infodbo.Licensing_Facts t1
INNER JOIN Infodbo.Inspections_Facts t2
ON t1.Licensing_Facts_Key=t2.Licensing_Facts_Key
INNER JOIN Infodbo.Licensing_Categories t3
ON t2.Licensing_Facts_Key=t3.Licensing_Facts_Key
group by Formatted_Licence_Number))

AND        t1.Type_Code = 'fp' 
AND        t1.Class_Code = 'health' 
AND        t3.Major_Code IN ('foodp1', 'foodp2', 'foodp3')
AND        t2.Result_Code IS NOT NULL

ORDER BY Formatted_Licence_Number 


and here are the results:

Inspection_Date Formatted_Licence_Number Licencee Description Major_Code
27/10/2008 FP11 A&B Trading Bakery P3
22/06/2009 FP11 A&B Trading Bakery P3
01/07/2011 FP102 C&D Trading Seafood P2
05/01/2011 FP102 C&D Trading Seafood P2
22/06/2009 FP102 C&D Trading Seafood P2
21/07/2009 FP1003 E&F Trading Coffee P2
02/08/2010 FP1003 E&F Trading Coffee P2


Can anyone tell me where I am going wrong?

Thanks in advance,

JR

Reply With Quote
  #7  
Old July 6th, 2013, 07:22 AM
3dwalkyantram 3dwalkyantram is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2013
Posts: 5 3dwalkyantram User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 2 m 48 sec
Reputation Power: 0
Selecting Max Date for Many Records

Selecting Max Date for Many Records- General SQL Development. Visit Dev Articles to discuss Selecting Max Date for Many Records.

Reply With Quote
  #8  
Old April 15th, 2014, 03:38 PM
tjsfury tjsfury is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Sep 2011
Posts: 1 tjsfury User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 45 sec
Reputation Power: 0
Quote:
Originally Posted by Euclid
I've got a table that's a list of clients waiting in line. Let's say the table has 4 fields:
(obligatory unique id),client name,worker, time

As the client's make their way though the facility, they see different workers and each worker makes a new entry in this table, with the current time, showing when they saw the client. Later on, this data will be used to analyse where clients spend the most time, so we can't overwrite any records.

What I need is a query that gets only the newest entry for each client.

so in a table like this:
bob, worker1, 1pm
bob, worker2, 2pm
jim, worker1, 3pm
jim, worker2, 4pm
jim, worker3, 5pm

The correct query would return only:
bob, worker2, 2pm
jim, worker3, 5pm

I've tried several different things with Max(time), and intersections, but I can't find a good solution. Surely there's a way to put it all in one sql statement.

Anyone have any ideas?
Thanks!


I am looking for a different way to do this but here is what I do in situations like this:

SELECT NAME, WORKER, TIME FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY TIME DESC) AS R
FROM TABLE_NAME
)A
WHERE R = 1

If anyone knows of a better way to do this let me know thanks.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesGeneral SQL Development > Selecting Max Date for Many Records


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap