Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group by with MAX then other columns - how to control what is returned if there are multiple results...


Group by with MAX then other columns - how to control what is returned if there are multiple results

Author
Message
kangarolf
kangarolf
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 205
Hi 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;



create table #items
(MANID INT,
DATEITEM DATETIME,
JOBID INT
)

insert into #items
(manid, dateitem, jobid)
select 1,'01/01/2013 10:20', 1
union all
select 1,'01/01/2013 10:20', 2
union all
select 2,'02/01/2013 10:20', 1
union all
select 3,'14/01/2013 10:20', 1
union all
select 3,'14/01/2013 10:20', 1
union all
select 4,'10/01/2013 10:20', 1
union all
select 4,'09/01/2013 10:20', 2

SELECT a.manid, a.JOBID, b.LASTDATE
FROM #ITEMS a INNER JOIN
(SELECT MANID, MAX(DATEITEM) AS LASTDATE
FROM #ITEMS
GROUP BY MANID) b on a.manid = b.manid and a.dateitem = b.lastdate
order by a.manid

select distinct a.manid, a.JOBID, b.LASTDATE
FROM #ITEMS a INNER JOIN
(SELECT MANID, MAX(DATEITEM) AS LASTDATE
FROM #ITEMS
GROUP BY MANID) b on a.manid = b.manid and a.dateitem = b.lastdate
order by a.manid



drop table #items



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

Thanks
Rolf



Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
To 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.


SELECT a.manid, MAX(a.JOBID) JOBID, b.LASTDATE
FROM #ITEMS a INNER JOIN
(SELECT MANID, MAX(DATEITEM) AS LASTDATE
FROM #ITEMS
GROUP BY MANID) b on a.manid = b.manid and a.dateitem = b.lastdate
GROUP BY a.manid, b.LASTDATE
order by a.manid




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
kangarolf
kangarolf
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 205
Thaks 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)..?

Rolf



Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
That 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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 134
Oops... duplicate post... sorry.
ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 134
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:

SELECT items.*
FROM #items items
LEFT OUTER JOIN #items items_new
ON items.manid=items_new.manid
AND items.jobid<items_new.jobid
WHERE items_new.manid IS NULL



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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
ryan.mcatee (11/6/2012)
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:



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:
http://www.sqlservercentral.com/articles/T-SQL/61539/


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
kangarolf
kangarolf
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 205
Whoa there.

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.


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

You did not know the ANSI/ISO syntax for insertion or the ISO-8601 date format. It is very useful:


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.

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:


Its an EXAMPLE!

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;



Correct me if I am wrong but OVER doesn't exist in Sql2000,7

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?


Please do explain to me why using DISTINCT would remove ONE of these rows

MANID, JOBID, DATE
1,1,2013-01-01
1,2,2013-01-01

So much, so wrong. Why do you wish to destroy truth?


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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search