InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:23:00 GMT20http://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspxWhoa there.[quote]It would really help if you had DDL that had keys, followed basic standards for dates, used a DATE data type, etc. This disaster is not even a table! Here is a wild guess as to how to make this mess into a schema. [/quote]Of course its not the actual table its just an example of the pertinent bits of it it doesn't need to be fully formed for this EXAMPLE[quote]You did not know the ANSI/ISO syntax for insertion or the ISO-8601 date format. It is very useful:[/quote]I know full well the structure of various ISO date formats, as I live in the UK its automatic for me to type out UK dates, as far as I understand it the dates are all stored as seconds since whenever anyway so ANY format you use is converted before its stored as a matter of course.[quote]Using alphabetic ordering for aliases is a hangover from the days of tape drives which had single letter names; an SQL programmer would use a meaningful name because we care about maintaining code. You can avoid the self join with current syntax: [/quote]Its an EXAMPLE![code="sql"]SELECT X.emp_id, X.job_id, X.posting_date FROM (SELECT emp_id, job_id, posting_date, MAX(posting_date) OVER (PARTITION BY emp_id) AS last_posting_date FROM Timesheets) AS X WHERE X.posting_date = X.last_posting_date;[/code]Correct me if I am wrong but OVER doesn't exist in Sql2000,7[quote]No, DISTINCT gets rid of redundant duplicates; this is a basic term in RDBMS; you need to learn it. Then you need to learn why SQL programmers would never have this problem in the first place. Keys? Normal forms? [/quote]Please do explain to me why using DISTINCT would remove ONE of these rowsMANID, JOBID, DATE1,1,2013-01-011,2,2013-01-01[quote]So much, so wrong. Why do you wish to destroy truth?[/quote]If you have nothing constructive to say please don't say anything. I'm shocked to receive such a response on such a friendly and genuinely expert forum, I'm not a DBA but I have to administrate and run a database as well as run a web server, write php, .net, t-sql, handle DNS and network infrastructure and I rely on picking expert brains in each of these fields when my jack-of-all trades knowledge fails me.Thu, 08 Nov 2012 02:40:11 GMTkangarolfhttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspx>> the situation is employment timesheets [no table for it?] and wanting to know the last job an employee completed an item in. Headache is that they complete these items in the back office en masse so there are lots of identical dates. <<It would really help if you had DDL that had keys, followed basic standards for dates, used a DATE data type, etc. This disaster is not even a table! Here is a wild guess as to how to make this mess into a schema. CREATE TABLE Timesheets(emp_id INTEGER NOT NULL, job_id INTEGER NOT NULL, PRIMARY KEY (emp_id, job_id), posting_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);You did not know the ANSI/ISO syntax for insertion or the ISO-8601 date format. It is very useful:INSERT INTO Timesheets (emp_id, job_id, posting_date)(1, 1, '2013-01-01'), (1, 2, '2013-01-01'), (2, 1, '2013-02-01'), (3, '2013-14-01', 1), -- (3, '2013-14-01', 1), <== redundant!! (4, 1, '2013-10-01'), (4, 2, '2013-09-01');Using alphabetic ordering for aliases is a hangover from the days of tape drives which had single letter names; an SQL programmer would use a meaningful name because we care about maintaining code. You can avoid the self join with current syntax: SELECT X.emp_id, X.job_id, X.posting_date FROM (SELECT emp_id, job_id, posting_date, MAX(posting_date) OVER (PARTITION BY emp_id) AS last_posting_date FROM Timesheets) AS X WHERE X.posting_date = X.last_posting_date;>> The DISTINCT keyword in the second query gets rid of identical rows but if the employee has completed items across multiple jobs that were all updated in the same process then we get two rows, one for each job. <<No, DISTINCT gets rid of redundant duplicates; this is a basic term in RDBMS; you need to learn it. Then you need to learn why SQL programmers would never have this problem in the first place. Keys? Normal forms? >> In this case I am not concerned with which one 'wins' as its only a rough guide as to where that employee was working last and if they update them all at the same time then I can not ever distinguish them <<So much, so wrong. Why do you wish to destroy truth?Wed, 07 Nov 2012 08:41:44 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspx[quote][b]ryan.mcatee (11/6/2012)[/b][hr]There are probably many ways to do this, and I'm not sure this is the proper way, but this is how I do queries like this:[/quote]That can help you, but it won't be great for performance.You're doing a triangular join and that's what some call "Hidden RBAR".Read the problems that this will cause in this article:[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]Tue, 06 Nov 2012 11:49:14 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspxThere are probably many ways to do this, and I'm not sure this is the proper way, but this is how I do queries like this:[code="sql"]SELECT items.*FROM #items itemsLEFT OUTER JOIN #items items_new ON items.manid=items_new.manid AND items.jobid<items_new.jobidWHERE items_new.manid IS NULL[/code]The "items.jobid<items_new.jobid" specification will ensure that you get the largest jobid for each manid. If you switched it to greater than, you would get the smallest jobid for each manid.Tue, 06 Nov 2012 11:44:42 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspxOops... duplicate post... sorry.Tue, 06 Nov 2012 11:40:23 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspxThat sounds complicated, but is hard to give an advice without knowing the whole situation.It would be a shot in the dark and might not be the most suitable advice.What works with one situation, might not work in another one.Mon, 05 Nov 2012 08:32:05 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspxThaks that makes perfect sense, I was trying to avoid more group bys.I need to collect a whole bunch more data in this query to do with totals and skills and other stuff..is it usually better to keep deriving tables that group by so you dont have to group by all columns (ie all the other stuff from other joined tables I'll be gathering from the MANID)..?RolfMon, 05 Nov 2012 08:26:02 GMTkangarolfhttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspxTo get only one row for every manid, you were really close. You just needed one more aggregate.If this doesn't solve your problem, I would need a better explanation on what do you want to do. You mention deleting, but I'm not sure if it's deleting or not showing.[code="sql"]SELECT a.manid, MAX(a.JOBID) JOBID, b.LASTDATEFROM #ITEMS a INNER JOIN(SELECT MANID, MAX(DATEITEM) AS LASTDATEFROM #ITEMSGROUP BY MANID) b on a.manid = b.manid and a.dateitem = b.lastdateGROUP BY a.manid, b.LASTDATEorder by a.manid[/code]Mon, 05 Nov 2012 08:22:22 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1381096-8-1.aspxHi all,I know this is a 'normal' issue that I see littered about the forums alot, trying to get unique data from a table based on a max query when you cant group by the columns you want out.I am joining back to the same table to get the extra columns I want but I'm not sure how to filter out multiple matches.The situation is employment timesheets and wanting to know the last job an employee completed an item in. Headache is that they complete these items in the backoffice en masse so there are lots of identical dates.Example data;[code]create table #items(MANID INT,DATEITEM DATETIME,JOBID INT)insert into #items(manid, dateitem, jobid)select 1,'01/01/2013 10:20', 1union allselect 1,'01/01/2013 10:20', 2union allselect 2,'02/01/2013 10:20', 1union allselect 3,'14/01/2013 10:20', 1union allselect 3,'14/01/2013 10:20', 1union allselect 4,'10/01/2013 10:20', 1union allselect 4,'09/01/2013 10:20', 2SELECT a.manid, a.JOBID, b.LASTDATEFROM #ITEMS a INNER JOIN(SELECT MANID, MAX(DATEITEM) AS LASTDATEFROM #ITEMSGROUP BY MANID) b on a.manid = b.manid and a.dateitem = b.lastdateorder by a.manidselect distinct a.manid, a.JOBID, b.LASTDATEFROM #ITEMS a INNER JOIN(SELECT MANID, MAX(DATEITEM) AS LASTDATEFROM #ITEMSGROUP BY MANID) b on a.manid = b.manid and a.dateitem = b.lastdateorder by a.maniddrop table #items[/code]The distinct keyword in the second query gets rid of identical rows but if the man has completed items across multiple jobs that were all updated in the same process then we get two rows, one for each job.In this case I am not concerned with which one 'wins' as its only a rough guide as to where that man was working last and if they update them all at the same time then I cant ever distinguish them anyways.How would remove one of the rows that have the same manid and date but different jobids..?ThanksRolfMon, 05 Nov 2012 08:12:06 GMTkangarolf