WOT: SQL query help needed again ... |
|
Porsche, and the Porsche crest are registered trademarks of Dr. Ing. h.c. F. Porsche AG.
This site is not affiliated with Porsche in any way. Its only purpose is to provide an online forum for car enthusiasts. All other trademarks are property of their respective owners. |
|
WOT: SQL query help needed again ... |
SirAndy |
Mar 26 2007, 04:41 PM
Post
#1
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
it's time for another SQL question ...
here's the problem: i have a table with event log information. it has a unique id for each entry (primary key), a user id, timestamp and other info. getting the info out of the table is easy except for one particular way. what i need is a page where i list all the last actions taken by any user per day. meaning, if users xyz created 30 logentries today, i only want to show the latest for any given day. so, the list should contain exactly ONE entry for each distinct user per day, showing his/her latest action ... something like this, except, the query below doesn't work ... (IMG:style_emoticons/default/rolleyes.gif) CODE SELECT * FROM tbl_EventLog WHERE iID IN ( SELECT DISTINCT el.iUserID, dummy.iID FROM tbl_EventLog as el INNER JOIN tbl_EventLog as dummy ON dummy.iUserID = el.iUserID WHERE (el.dtCreated >= '3/26/2007 0:00:01 AM') ) anyone? (IMG:style_emoticons/default/confused24.gif) Andy |
Air_Cooled_Nut |
Mar 26 2007, 05:14 PM
Post
#2
|
914 Ronin - 914 owner who lost his 914club.com Group: Members Posts: 1,748 Joined: 19-April 03 From: Beaverton, Oregon Member No.: 584 Region Association: None |
Can you use a MAX clause on the date/time stamp? That would probably eliminate the need for the DISTINCT clause.
|
SirAndy |
Mar 26 2007, 05:24 PM
Post
#3
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
Can you use a MAX clause on the date/time stamp? That would probably eliminate the need for the DISTINCT clause. i don't see how using MAX on the date/timestamp would yield a resultset with only one entry per unique user ... (IMG:style_emoticons/default/idea.gif) Andy |
fiid |
Mar 26 2007, 05:25 PM
Post
#4
|
Turbo Megasquirted Subaru Member Group: Members Posts: 2,827 Joined: 7-April 03 From: San Francisco, CA Member No.: 530 Region Association: Northern California |
(IMG:style_emoticons/default/agree.gif) He's right - we have to do something similar in our software - you do max(date) where date is less than the end of the day you're interested in.
|
fiid |
Mar 26 2007, 05:25 PM
Post
#5
|
Turbo Megasquirted Subaru Member Group: Members Posts: 2,827 Joined: 7-April 03 From: San Francisco, CA Member No.: 530 Region Association: Northern California |
I think you might group by userid - but not sure....
|
blitZ |
Mar 26 2007, 05:26 PM
Post
#6
|
Beer please... Group: Members Posts: 2,223 Joined: 31-August 05 From: Lawrenceville, GA Member No.: 4,719 Region Association: South East States |
The Max(dtCreated ) would probably work, then group by user and log ID. You will then have the IDs you need to get the details you require.
|
SirAndy |
Mar 26 2007, 06:01 PM
Post
#7
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
The Max(dtCreated ) would probably work, then group by user and log ID. You will then have the IDs you need to get the details you require. that's what i was trying to avoid ... i hate looped queries. i'm looking for a single query that will give me a single resultset to work with. for now, i have created a nested loop (yuk (IMG:style_emoticons/default/barf.gif) ) and that works, but it seems like there should be a better way ... here's what i have right now (works, but is ugly (IMG:style_emoticons/default/icon8.gif) ) CODE query1 = SELECT DISTINCT iUserID FROM tbl_EventLog WHERE dtCreated >= '03/26/2007 00:00:01 AM' ORDER BY iUserID do while not query1.EOF query2 = SELECT TOP 1 el.*, iut.sName as sType FROM tbl_EventLog as el INNER JOIN tbl_INFO_UserTypes as iut ON iut.iID = el.iUserType WHERE (el.iUserID = query1.iUserID) AND (el.dtCreated >= '03/26/2007 00:00:01 AM') ORDER BY el.dtCreated DESC ... do something here with result ... query1.MoveNext loop anything better would be good. anyone? (IMG:style_emoticons/default/pray.gif) Andy |
drewvw |
Mar 26 2007, 06:10 PM
Post
#8
|
new england car guy Group: Members Posts: 1,631 Joined: 24-February 06 From: Boston, MA Member No.: 5,630 Region Association: North East States |
Not sure what DB you are using, but any thoughts on dropping that into a stored proc?
that way you can abstract the looping to the DB layer and return a single result set. Performance would probably be better as well. |
SirAndy |
Mar 26 2007, 06:29 PM
Post
#9
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
Not sure what DB you are using, but any thoughts on dropping that into a stored proc? that way you can abstract the looping to the DB layer and return a single result set. Performance would probably be better as well. yeah, i could. MS-SQL 8 Server. problem is, the server app is HUGE and was entirely done in ASP without a single stored procedure. it's all inline ASP queries. if i start using stored procedures now, i'm afraid i'll confuse all the other programmers even more than they already are. we're talking hundreds of thousands of lines of code ... (IMG:style_emoticons/default/rolleyes.gif) Andy |
SirAndy |
Mar 26 2007, 06:31 PM
Post
#10
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
plus, i just noticed, with my hacked solution, i can't sort the result by date ...
(IMG:style_emoticons/default/headbang.gif) Andy |
drewvw |
Mar 26 2007, 06:42 PM
Post
#11
|
new england car guy Group: Members Posts: 1,631 Joined: 24-February 06 From: Boston, MA Member No.: 5,630 Region Association: North East States |
yeah, i could. MS-SQL 8 Server. problem is, the server app is HUGE and was entirely done in ASP without a single stored procedure. it's all inline ASP queries. if i start using stored procedures now, i'm afraid i'll confuse all the other programmers even more than they already are. we're talking hundreds of thousands of lines of code ... (IMG:style_emoticons/default/rolleyes.gif) Andy That's a really good point. A lot of the legacy ASP code where I work is a mix of overly complex stored procs and inline queries. Its a mess for others to figure out (READ: me the last 2 months). Do we have any true DBAs in the club or just programmers who dabble in DB? |
watsonrx13 |
Mar 26 2007, 06:52 PM
Post
#12
|
Advanced Member Group: Members Posts: 2,734 Joined: 18-February 03 From: Plant City, FL Member No.: 312 Region Association: South East States |
Andy, what's the fields that you're trying to return?
If you only wanted the userid and date/time then.... Select MAX(date/time),userid from xxxx where date/time between fromdate and todate group by userid; BTW, I'm a PL/SQL Oracle developer.... -- Rob |
SirAndy |
Mar 26 2007, 06:53 PM
Post
#13
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
Do we have any true DBAs in the club or just programmers who dabble in DB? no real DBA's here ... this is just a project i'm doing working as a contractor. they brought me in for the project because nobody here inhouse can handle the scope ... lots of work but good money and otherwise easy. i do easier SQL all day long, it's the more complicated queries where my lack of edufication shows. should have taken those damm classes 20 years ago. went drinking instead ... (IMG:style_emoticons/default/biggrin.gif) Andy |
drewvw |
Mar 26 2007, 06:59 PM
Post
#14
|
new england car guy Group: Members Posts: 1,631 Joined: 24-February 06 From: Boston, MA Member No.: 5,630 Region Association: North East States |
I am the same way....I know enough DB/SQL to be dangerous but its not my strength by a long shot. I can ask one guy I know tomorrow who might have a nifty solution for this.... |
SirAndy |
Mar 26 2007, 07:02 PM
Post
#15
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
Andy, what's the fields that you're trying to return? If you only wanted the userid and date/time then.... Select MAX(date/time),userid from xxxx where date/time between fromdate and todate group by userid; BTW, I'm a PL/SQL Oracle developer.... -- Rob yeah, thought of that but won't work. i need all the columns in the table for each result. the problem with "GROUP BY" as well as "DESTINCT" is that you can't have any other fields returned unless you add them to the "GROUP BY". which renders the query useless ... (IMG:style_emoticons/default/headbang.gif) Andy PS: what i need: - go through all rows in table tbl_EventLog - find all entries from today - narrow down to last entry for each user - sort by time (DESC) each user has many entries in the event log every day, but the list should only show their latest activity ... the output will look like this (note that the list *should* be sorted by time, but it isn't right now): |
SirAndy |
Mar 26 2007, 07:38 PM
Post
#16
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
well, at least i got the list ordered by time now ...
still not pretty, looping queries and all, but it works, i guess ... (IMG:style_emoticons/default/dry.gif) Andy CODE SELECT iUserID, max(dtCreated) as dtMax FROM tbl_EventLog GROUP BY iUserID ORDER BY dtMax DESC this gives me a initial resultset with all the user ids sorted by date. i now can loop through all of these and find the latest entries for the day ... |
blitZ |
Mar 26 2007, 08:09 PM
Post
#17
|
Beer please... Group: Members Posts: 2,223 Joined: 31-August 05 From: Lawrenceville, GA Member No.: 4,719 Region Association: South East States |
Hmm, wouldn't something like this work?
SELECT * FROM tbl_EventLog WHERE iID IN ( select iID from ( SELECT iUserID, iID, max(dtCreated) as lastEntry FROM tbl_EventLog WHERE dtCreated >= '3/26/2007 0:00:01 AM' group by iUserID, iID ) dTable ) order by dtCreated |
SirAndy |
Mar 26 2007, 08:22 PM
Post
#18
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
Hmm, wouldn't something like this work? SELECT * FROM tbl_EventLog WHERE iID IN ( select iID from ( SELECT iUserID, iID, max(dtCreated) as lastEntry FROM tbl_EventLog WHERE dtCreated >= '3/26/2007 0:00:01 AM' group by iUserID, iID ) dTable ) order by dtCreated no suck luck ... (IMG:style_emoticons/default/sad.gif) tried it, returns the full set of 4000+ entries in the eventlog for today. keep 'em coming ... (IMG:style_emoticons/default/type.gif) Andy |
ClayPerrine |
Mar 26 2007, 08:47 PM
Post
#19
|
Life's been good to me so far..... Group: Admin Posts: 15,514 Joined: 11-September 03 From: Hurst, TX. Member No.: 1,143 Region Association: NineFourteenerVille |
Andy,
Look up a tool on Microsoft's website called "eventcombMT". It will parse the event logs on any server, and return them by date, type and user, or any other field. It also doesn't require a table be imported to SQL, just query them live. Embed the app in a stored procedure, and feed it the proper switches. |
SirAndy |
Mar 26 2007, 09:00 PM
Post
#20
|
Resident German Group: Admin Posts: 41,676 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
Andy, Look up a tool on Microsoft's website called "eventcombMT". It will parse the event logs on any server, and return them by date, type and user, or any other field. It also doesn't require a table be imported to SQL, just query them live. Embed the app in a stored procedure, and feed it the proper switches. this is *NOT* a windows event log !!! it's just a DB table called tbl_EventLog and has absolutely NOTHING to do with the OS eventlog! it logs events of peoples (users) interaction with the B2B website ... (IMG:style_emoticons/default/bye1.gif) Andy PS: it's *NOT* a webserver log either! this is a completely proprietary format used by the B2B website to track specific events on the user pages. it's populated by the ASP code and i need to build a page in the admin section that allows to break down the log by user/date ... |
Lo-Fi Version | Time is now: 8th June 2024 - 02:37 AM |
All rights reserved 914World.com © since 2002 |
914World.com is the fastest growing online 914 community! We have it all, classifieds, events, forums, vendors, parts, autocross, racing, technical articles, events calendar, newsletter, restoration, gallery, archives, history and more for your Porsche 914 ... |