select Distinct not working like mysql.

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

SQL Server 2005 Forums

Transact-SQL (2005)

select Distinct not working like mysql.

Author

Topic

darkjunky
Starting Member

4 Posts

Posted - 2012-05-24 : 06:48:14

Hello I have a problem that is making my head hurt. I'm a little bit worried I'm going to be told MS SQL just can't do it but here goes..

I'm creating a view for employees some of which have more then job role so.. job title, department, line manager and hours worked are different while all the rest of the information im selecting is the same.

Now I've written a query that selects all of the employees and matches though and find all of thier post information and now I simply want to limit the view to show only the record with the highest hours worked.

In mysql I could just do the following

group by empID
order by hours worked DSC

and it would just give me what I need however ms sql just breaks down at this point.

So is there a way I can do this? without having to write nested selects for every field that has muiltpe records and everytime write all matching joins.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-05-24 : 07:34:55

Theres many ddifferent ways to do this kind of thing, you could seelct MAX() use ROW_NUMBER(), as you say, nest the joins etc..

Post some sample structure/data and expected results and you'll even probably get it written for you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-24 : 07:39:21

you can do that in ms msql, but you need the top clause
SELECT TOP 100 PERCENT
FROM yourTable
GROUP BY empID
ORDER BY HoursWorked DESC

Jim

Everyday I learn something that somebody else already knew

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-24 : 07:42:03

I don't mean to be rude or offensive (really) but I really think that this is a matter of coding technique and not a matter of what MySQL/SQL Server can and cannot do.

Post your MySQL query and I'm 100% positive that it can be solved equally or better in T-SQL.

- Lumbago
My blog-> http://thefirstsql.com

darkjunky
Starting Member

4 Posts

Posted - 2012-05-24 : 08:00:44

quote:
Originally posted by Lumbago

I don't mean to be rude or offensive (really) but I really think that this is a matter of coding technique and not a matter of what MySQL/SQL Server can and cannot do.

Post your MySQL query and I'm 100% positive that it can be solved equally or better in T-SQL.

- Lumbago
My blog-> http://thefirstsql.com

@Lumbadgo None taken, I'm totally confused because the MySQL way of build the query just doesn't work.. So my sql query is pretty weak since I'm used to MySQL. I can't really post the MySQL for it since it's MSSQL so I write roughly what I would normally do.

@Jim that doesn't seem to work or rather this is where I'm confused because mysql just does it but mssql is crying about "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" on every other field I'm trying to select.

I don't want to aggregate or group my results on anything else I just (empID) then I want to take the top record based on the order.

So

select Distinct
CAST (Person.Details."Person Number" AS varchar) as empID,
Person.Details."Title" as empTitle,
Person.Details."First Name" as empFirstName,
Person.Details."Known As" as empKnownAs,
Person.Details."Surname" as empSurname,
Person.Details."Initials" as empInitails,
Person.Details."E-Mail" as empEmail,
Person.Details."Work Phone Number" as empPhoneNum,
Person.Details."Work Extension Number" as empExtNum,
Person."Details Custom"."Room Number" as empRoomNum,
Person."Details Custom"."Line Manager" as empIsLineManager,
Person."Details Custom"."SMT or CMT" as empSMTorCMT,
Person."Details Custom"."Departmental Administrator" as empDepartmentalAdmin,
Employee.Person."Continuous Service Date" as empStartDate,
Employee.Person."Fire Officer" as empFireOfficer,
Employee.Person."First Aider" as empFirstAider,
Employee.Person."Health and Safety Officer" as empHealthOfficer,
Organisation."Post Details"."Post Name" as empPostName,
Organisation."Post Details"."Manager Post Number" as empLineMangerPostID,
Organisation.Locations."Location Name" as empSite,
orgDetails."Unit Name" as empDepartment,
Employee."Career History"."FTE"  as empFTE,
carHistExt."End Date" as empExpiryDate,
CAST (manDet."Person Number" AS varchar) as empLineManagerEmpID,
Employee."Appointment History"."Appointment Number"

FROM Person.Details 

INNER JOIN Person."Details Custom"
ON Person.Details."Person Number" = Person."Details Custom"."Person Number"  

JOIN Employee.Person
ON Person.Details."Person Number" = Employee.Person."Person Number"

JOIN Employee."Appointment History"
ON Employee."Appointment History"."Person Number" = Person.Details."Person Number"

JOIN Employee."Career History"
ON  Employee."Career History"."Appointment Number" = Employee."Appointment History"."Appointment Number"

JOIN Organisation."Post Details"
ON Organisation."Post Details"."Post Number" = Employee."Career History"."Post Number" 

JOIN Pattern.Details as patDetails
ON Employee."Career History"."Pattern Number" = patDetails."Pattern Number"

JOIN Organisation.Locations
ON Employee."Career History"."Location Number" = Organisation.Locations."Location Number"

JOIN Organisation.Details as orgDetails
ON Organisation."Post Details"."Parent Unit Number" = orgDetails."Unit Number"

JOIN Employee."Appointment History Extra Detail" 
ON Employee."Appointment History Extra Detail"."Appointment Number" = Employee."Appointment History"."Appointment Number"

JOIN Employee."Career History Extra Detail"
ON Employee."Career History Extra Detail"."Post ID" = Employee."Appointment History Extra Detail"."Current Post ID"

JOIN Employee."Appointment History Extra Detail" as test
ON Employee."Career History Extra Detail"."Post Number" = Organisation."Post Details"."Post Number" 

/* manager information */

JOIN Employee."Career History" as manCar
ON manCar."Post Number" = Organisation."Post Details"."Manager Post Number" 

JOIN Employee."Appointment History" as manApp
ON  manCar."Appointment Number" = manApp."Appointment Number"

JOIN Employee."Appointment History Extra Detail" as AppHistExt
ON AppHistExt."Appointment Number" = manApp."Appointment Number"

JOIN Employee."Career History Extra Detail" as carHistExt
ON carHistExt."Post ID" = AppHistExt."Current Post ID"  AND carHistExt."End Date" IS NULL

JOIN Employee."Career History" as manCar2
ON carHistExt."Career Number" = manCar2."Career Number"

JOIN Employee."Appointment History" as manApp2
ON manApp2."Appointment Number" = manCar2."Appointment Number"

JOIN Employee.Person as manDet
ON manApp2."Person Number" = manDet."Person Number"

WHERE Organisation."Post Details"."Expiry Date" > GetDate() 
OR Organisation."Post Details"."Expiry Date" is NULL
AND empID = '13714'
order by empFTE desc

This is my current query. I've add on the AND empID = to a record that has more then 1 post.

This results in this;


13714	Mr	Darren	Darren	XXXXXXX	DL	DXXXXXXX@XXXXXXX	NULL		NULL	NULL	NULL	NULL	2007-11-16 00:00:00.000	0	0	0	Associate Lecturer	3926	Road	Academy of Education, Social Science, Health and Creative Industries	0.60386473	NULL	16011	15322	
13714	Mr	Darren	Darren	XXXXXXX	DL	DXXXXXXX@XXXXXXX	NULL		NULL	NULL	NULL	NULL	2007-11-16 00:00:00.000	0	0	0	Part-time Lecturer	3492	Road	Academy of Public Services and Business	0.09178744	NULL	15257	18338

Which is fine, but what I really need is to add FTE together for the records and then take the rest of the information from the record with the hights FTE. So that the view only contains a single record per person.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources